Sql – Indirect Foreign Key

You should always enforce referential integrity by using “normal” FOREIGN KEYs. In a nutshell, FOREIGN KEYs have the following advantages: They are already implemented within the DBMS. They are declarative, self-documenting and “obvious”. They cannot be bypassed (unless explicitly disabled or dropped). They are correct. They are fast. They support cascading referential actions (such as … Read more

Same data from different entities in Database – Best Practice – Phone numbers example

In most cases . . . “Staff” always describes people. Some customers are people. Some customers are businesses (organizations). “Suppliers” are usually (always?) organizations. Staff can also be customers. Suppliers can also be customers. There are serious problems with having separate tables of staff phone numbers, supplier phone numbers, and customer phone numbers. Staff can … Read more

designing database to hold different metadata information

This is called the Observation Pattern. Three objects, for the example Book Title=”Gone with the Wind” Author=”Margaret Mitchell” ISBN = ‘978-1416548898’ Cat Name=”Phoebe” Color=”Gray” TailLength = 9 ‘inch’ Beer Bottle Volume = 500 ‘ml’ Color=”Green” This is how tables may look like: Entity EntityID Name Description 1 ‘Book’ ‘To read’ 2 ‘Cat’ ‘Fury cat’ 3 … Read more