Paul, > Thanks guys. I thought that normalization would refer to redundant info not > necessarily the same column names? I would further see the problem with the > design if say phone_number in the agent table and lawyer table contained the > same data, which of course they won't.
The layout would basically look like this: - table agent - id - name - table lawyer - id - name - table contact - id - street - city - phone All information that is specific for agents only would go to the agent table, and ditto for the lawyer table. All _kinds_ of information both have in common would go to the contact table. In relational databases, "things" like this are called "entities". > I thought about the "persons" table instead however you might notice that > there are some columns that each do not have. Ie. home and work numbers for > vendors and purchasers, firm for lawyers etc. Also I am expecting several > columns to be NULL values for vendors and purchasers but not the other > persons. See above; what's specific for a particular group of people can be regarded as an entity, and will therefore go to a specific table, rather than to a "lookup table" like contact. > I am very concerned with how I have setup the PK/FK/indices! Basically, "id" is what your primary/foreign keys would be. In the above example, agent.id and lawyer.id would both be primary keys, while contact.id would become a foreign key. In your application, and/or through foreign key constraints in MySQL, just make sure you cannot delete an entry that is associated with an id in one of the parent tables (ON DELETE RESTRICT), or that deletions also delete entries in the parent tables, or the other way around (ON DELETE CASCADE). Same applies to UPDATEs: http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Also, remember that you cannot use foreign key constraints (in a meaningful way) with MyISAM tables, but that you should use InnoDB tables instead: http://www.mysql.com/doc/en/Using_InnoDB_tables.html Regards, -- Stefan Hinz <[EMAIL PROTECTED]> iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
