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.