Simple approach to relational database design
Posted by Vijay Kumar on February 14, 2007
First, create a list of important things (entities) and include those things you may not initially believe is important. Second, draw a line between any two entities that have any connection whatsoever; except that no two entities can connect without a ‘rule’; e.g.: families have children, employees work for a department. Therefore put the ‘connection’ in a diamond, the ‘entities’ in squares. Third, your picture should now have many squares (entities) connected to other entities through diamonds (a square enclosing an entity, with a line to a diamond describing the relationship, and then another line to the other entity). Fourth, put descriptors on each square and each diamond, such as customer — airline — trip. Fifth, give each diamond and square any attributes it may have (a person has a name, an invoice has a number), but some relationships have none (a parent just owns a child). Sixth, everything on your page that has attributes is now a table, whenever two entities have a relationship where the relationship has no attributes, there is merely a foreign key between the tables. Seventh, in general you want to make tables not repeat data. So, if a customer has a name and several addresses, you can see that for every address of a customer, there will be repeated the customer’s first name, last name, etc. So, record Name in one table, and put all his addresses in another. Eighth, each row (record) should be unique from every other one; Mr. Freedman suggests a ‘auto-increment number’ primary key, where a new, unique number is generated for each new inserted row. Ninth, a key is any way to uniquely identify a row in a table…first and last name together are good as a ‘composite’ key. That’s the technique.