Which is better? I guess that all depends on your entity relationships.
If I assume that each PERSON would have only 1 USER record and each USER
would match to only 1 PERSON record. That is a "1-to-1 relationship" or
(1..1) and is typically stored with all of the necessary column in just one
table (exceptions to this do exist and for excellent reasons). So you
would probably only need either a PERSON or a USER table but not both.
If each PERSON could have more than 1 USER record (or each USER record
could match to more than 1 PERSON record) then you have a "1-to-many
relationship" or (1..n). In a (1..n) situation your "many" table would
have a column that would contain the PK value(s) of the "one" table. For
this example your USER table would have a column to contain a PERSON_ID so
that each person could match with multiple users.
The last situation is (n..n) or "many-to-many". Let's assume that each
PERSON belongs to 1 or more COMPANY(s) and each COMPANY contains more than
1 PERSON. This will require 3 tables to model, a PERSON table, a COMPANY
table, and a PERSON_COMPANY table (a "mapping" table) that looks something
like this:
CREATE TABLE PERSON_COMPANY (
PERSON_ID int not null,
COMPANY_ID int not null,
PRIMARY KEY(PERSON_ID, COMPANY_ID)
)
There would be 1 record in PERSON_COMPANY for each person-company
relationship. If the same person worked for 3 companies, there would be 3
records with that person's PERSON_ID but each row would have a different
COMPANY_ID. In the event you had multiple columns as PRIMARY KEYS in your
"many" tables, your "mapping" table would also have to have all of those
columns from each side of the relationship.
So long as you use the right table models to match your relationships, your
database should have the right balance between speed and size. JOIN-ing
tables on indexed columns (like the PRIMARY KEY columns) is VERY fast and
you should have excellent response from your database.
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Westrmi"
<[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>
com> cc:
Fax to:
06/15/2004 11:45 Subject: DB Design: performance
question
AM
Hi everybody, I'm newly subscribed so please be gentle :)
I have a design problem whose solution seems to be directly linked to
the MySQL way of handling joints.
I created a table USER which manages all entities allowed to connect to
the system.
|---------------| |---------------|
|PERSON | |COMPANY |
|id_person | |id_company |
|id_user | |id_user |
|... | |... |
|---------------| |---------------|
| |
| ------------------
| |
V V
|-------|
|USER |
|id_user|
|... |
|-------|
A professional told me tables PERSON and COMPANY would slow down the
querys (I should by the way move informations of PERSON and COMPANY to
USER) and increase upkeep costs. We began to argue as I told queries on
USER weren't affected by them (as FK's are placed on PERSON and
COMPAGNY). Moreover, my solution is more formal and obeys normalisation.
Which solution seems to be better ?
Thanks by advance
_____________________________________________________________________
Un mot doux à envoyer? Une sortie ciné à organiser? Faites le en temps
réel avec MSN Messenger! C'est gratuit! http://ifrance.com/_reloc/m
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
