On 7/21/2010 1:44 PM, Marc Guay wrote:
Hi everyone,
I have a question regarding database design, I hope that this is
appropriate for the list. Let's say that I have the following tables:
clients (id,name)
contacts (id, name, phone, client_id (FK))
companies (id, name)
employees (id, name, phone, company_id (FK))
logins (id, username, password)
What's the best way to connect contacts and employees to the logins
table? I've thought of duplicating the username & password fields
into both the contacts and employees tables, adding both contact_id
and employee_id foreign keys to the logins table, and adding login_id
foreign keys to the contacts and employees tables, but none of these
solutions seem very smart.
Normalization rules still apply.
If everyone is a LOGIN, that is your top-most table.
If a CLIENT is a LOGIN with a NAME added to it, then `clients` becomes
(id, login_id (fk), name)
If a contact is a CLIENT with a phone number added to it, then
`contacts` becomes
(id, client_id (fk), phone)
In theory, that will give you accurate storage with the least
duplication. In practice, however, you can absolutely "over-normalize"
data to the point where all you do is write JOIN queries and it takes
forever to get information out of the database.
So the trick is to find the right balance between theory and performance.
In practice, look at what actions you need and how you can segregate
your data based on how the data is used. If you need to search BOTH
lists of people (clients and contacts) frequently, then the normalized
approach works just fine (all names are in one table). If you do not,
the segregating that data into two tables may work out better.
Careful de-normalization can result in speed improvements. However I
suggest strongly that all data designs should start normalized and only
de-normalize where the physical components of performance indicate the
need to do so.
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
