Mysql – Object-oriented programming CRUD design pattern

MySQLoraclepostgresql

At first I have posted this question at https://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern – that's because I think it's a subect question, and it can not answered by "yes" or "no" or with a single answer.

I don't know if here it's the better place for this question, but here we go. I have shared a common question for DB designers – imagine the following SELECT:

 SELECT
     P.id,
     P.legalName,
     P.type,
     L.tradeName,
     L.foundation,
     L.EIN,
     N.SSN,
     N.gender,
     N.birthDay,
     A.zipCode,
     A.address,
     A.number,
     A.district,
     A.city,
     A.state
 FROM
     Person as P

 LEFT JOIN
     LegalEntity AS L ON L.person = P.id AND P.type = 'L'

 LEFT JOIN
     NaturalPerson as N ON N.person = P.id AND P.type = 'N'

 LEFT JOIN
     Address as A ON A.person = P.id AND E.legalAddress = 1

The above SELECT has a "maintaining" level relatively high by the following factors:

CREATE – When a Person are inserted in the database the next insert will be based on Person.type (L => Legal or N => Natural) that defines if will create a new record on LegalEntity or NaturalPerson with Person last inserted id as the foreign key.
READ – To provide a consistent data, we use JOINS with aditional term "AND T.type" for LegalEntity and NaturalPerson to return only relative data to Person.type FLAG. For eg. if Person it's a LegalEntity than we have EIN (Employer Identification Number or Federal Tax Id), if not, it's a NaturalPerson than have SSN (Security Social Number)
UPDATE – When you perform an update is needed to test whether there is specialization. This is because a record could originally have been created as type = N (Natural) and later want to upgrade it to Corporate (L = Legal). An INSERT to create original NaturalPerson was made, and now with the exchange of nature type is necessary to exclude logically or physically the NaturalPerson record and then create the new record of LegalEntity.
DELETE – WITHOUT aditional complexities.

Imagining all this complexity is also necessary. Now think that be need to run in 3 differente SGDBs (MySQL, Oracle and Postgres) – the quoted example are created in MySQL.

What are the best practices that we can adopt in this scenario?
Maintenance of data with the triggers assist for the 3 SGDS?
Create a CRUD with appropriate treatments implemented via application?

Thank you in advance for your time.

Best Answer

CREATE - When a Person are inserted in the database the next insert will be based on Person.type (L => Legal or N => Natural) that defines if will create a new record on LegalEntity or NaturalPerson with Person last inserted id as the foreign key.

Do all inserts for each "person" in a single transaction based on updatable views, not on base tables. Every dbms exposes a function that will return the last inserted ID number. (For PostgreSQL, this SO answer.)

READ - To provide a consistent data, we use JOINS with aditional term "AND T.type" for LegalEntity and NaturalPerson to return only relative data to Person.type FLAG. For eg. if Person it's a LegalEntity than we have EIN (Employer Identification Number or Federal Tax Id), if not, it's a NaturalPerson than have SSN (Security Social Number)

In the query you posted, you don't do that at all. Instead, you retrieve all the data for every type.

Instead, use two views (see above), one for legal persons and one for natural persons. Resist the urge to join every possible table in the views.

UPDATE - When you perform an update is needed to test whether there is specialization. This is because a record could originally have been created as type = N (Natural) and later want to upgrade it to Corporate (L = Legal). An INSERT to create original NaturalPerson was made, and now with the exchange of nature type is necessary to exclude logically or physically the NaturalPerson record and then create the new record of LegalEntity.

In the real world, you don't "upgrade" a natural person to be a corporation. (A person who "is" a corporation is legally two different things. That's why there are corporations.) In a database, if you run into such a situation, you have to take one of three paths.

All the original data is wrong, because you entered data for a person who is not really a person. Delete the bad data, and enter new data for the corporation.
All the original data is right; that person is actually a natural person. Reject all the updates that would make that natural person a corporation.
All the original data is right, and that person is both a natural person and a corporation. Redesign your schema to accommodate this possibility.

Related Solutions

Mysql – How to select the latest record having one state where no later records exist with any other state

SELECT widget, MAX(`timestamp`) AS ts
FROM tableX AS t
WHERE state = 'down'
GROUP BY widget
HAVING NOT EXISTS
       ( SELECT *
         FROM tableX AS tt
         WHERE tt.widget = t.widget
           AND tt.state <> 'down'
           AND tt.`timestamp` > MAX(t.`timestamp`)
       ) ;

I think that you'll need two indices, one on (widget, state, timestamp) and one on (widget, timestamp, state) for efficiency.

This will work, too, and will be needing only one index, on (widget, timestamp, state):

SELECT t.widget, t.`timestamp`
FROM 
        tableX AS t
    JOIN
        ( SELECT widget, MAX(`timestamp`) AS ts
          FROM tableX
          GROUP BY widget
        ) AS tm
            ON  tm.widget = t.widget
            AND tm.ts = t.`timestamp`
WHERE t.state = 'down' ;

Tested both at SQL-Fiddle: test

Mysql – Product Attribute List Design Pattern

I personally would use a model similar to the following:

The product table would be pretty basic, your main product details:

create table product
(
  part_number int, (PK)
  name varchar(10),
  price int
);
insert into product values
(1, 'product1', 50),
(2, 'product2', 95.99);

Second the attribute table to store the each of the different attributes.

create table attribute
(
  attributeid int, (PK)
  attribute_name varchar(10),
  attribute_value varchar(50)
);
insert into attribute values
(1, 'color', 'red'),
(2, 'color', 'blue'),
(3, 'material', 'chrome'),
(4, 'material', 'plastic'),
(5, 'color', 'yellow'),
(6, 'size', 'x-large');

Finally create the product_attribute table as the JOIN table between each product and its attributes associated with it.

create table product_attribute
(
  part_number int, (FK)
  attributeid int  (FK) 
);
insert into product_attribute values
(1,  1),
(1,  3),
(2,  6),
(2,  2),
(2,  6);

Depending on how you want to use the data you are looking at two joins:

select *
from product p
left join product_attribute t
  on p.part_number = t.part_number
left join attribute a
  on t.attributeid = a.attributeid;

See SQL Fiddle with Demo. This returns data in the format:

PART_NUMBER | NAME       | PRICE | ATTRIBUTEID | ATTRIBUTE_NAME | ATTRIBUTE_VALUE
___________________________________________________________________________
1           | product1   | 50    | 1           | color          | red
1           | product1   | 50    | 3           | material       | chrome
2           | product2   | 96    | 6           | size           | x-large
2           | product2   | 96    | 2           | color          | blue
2           | product2   | 96    | 6           | size           | x-large

But if you want to return the data in a PIVOT format where you have one row with all of the attributes as columns, you can use CASE statements with an aggregate:

SELECT p.part_number,
  p.name,
  p.price,
  MAX(IF(a.ATTRIBUTE_NAME = 'color', a.ATTRIBUTE_VALUE, null)) as color,
  MAX(IF(a.ATTRIBUTE_NAME = 'material', a.ATTRIBUTE_VALUE, null)) as material,
  MAX(IF(a.ATTRIBUTE_NAME = 'size', a.ATTRIBUTE_VALUE, null)) as size
from product p
left join product_attribute t
  on p.part_number = t.part_number
left join attribute a
  on t.attributeid = a.attributeid
group by p.part_number, p.name, p.price;

See SQL Fiddle with Demo. Data is returned in the format:

PART_NUMBER | NAME       | PRICE | COLOR | MATERIAL | SIZE
_________________________________________________________________
1           | product1   | 50    | red   | chrome   | null
2           | product2   | 96    | blue  | null     | x-large

As you case see the data might be in a better format for you, but if you have an unknown number of attributes, it will easily become untenable due to hard-coding attribute names, so in MySQL you can use prepared statements to create dynamic pivots. Your code would be as follows (See SQL Fiddle With Demo):

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(a.attribute_name = ''',
      attribute_name,
      ''', a.attribute_value, NULL)) AS ',
      attribute_name
    )
  ) INTO @sql
FROM attribute;

SET @sql = CONCAT('SELECT p.part_number
                    , p.name
                    , ', @sql, ' 
                   from product p
                   left join product_attribute t
                     on p.part_number = t.part_number
                   left join attribute a
                     on t.attributeid = a.attributeid
                   GROUP BY p.part_number
                    , p.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This generates the same result as the second version with no need to hard-code anything. While there are many ways to model this I think this database design is the most flexible.

Best Answer

Related Solutions

Mysql – How to select the latest record having one state where no later records exist with any other state

Mysql – Product Attribute List Design Pattern

Related Question