The following is my personal opinion on potential errors in ER modeling and how to design a database: It is very important to stick to normalization and best practices.
Two common errors in ER modeling include data anomalies and redundant data. Lets assume we have a table named EMPLOYEE_V1 that requires you to input who an employee is married by name in a data field. If one of the employees divorces the other employee then the two records must be updated (returned to NULL). If only one is updated, then inconsistent data exists. This problem becomes worse if several divorced employees decide to marry each other. The divorces can also cause undesirable nulls for employees who are not married to other employees in the company.
However, If you decided to implement two tables, rather than one, redundant data becomes the issue. For instance, if you had another table that shows which employees are wedded to each other solely based on ID, then you can have repeated data when item 345 shows up married to 347 and item 347 is married to 345. It will show up as two marriages, not just one.
Now what would I do? I would advise the designer to ask questions regarding the relationship rules and business rules behind the database.
Questions that should be asked include: What additional data is required to be collected about the marriages? Do the tables use the same terms to describe the data? How should NULL be defined for this design? How do you ensure that every marriage is a unique event and not duplicated?
07
Dec