Help on a couple of tables for ERD

Datanalysis · #1 (**permalink**) 10-13-08, 01:10

Designing an ERD for a pet shop for school that does boarding and grooming. On the ERD we have the pet table with "pet_id" that has a many to many relationship with the color table that has colors and an attribute "color_id". Between them we have a composite table or "junction table" with both pet and color id as Foreign Keys and an artifical petcolor_id field as an Primary Key. Do we need this made up petcolor_id primary key field in there?

r937 · #2 (**permalink**) 10-13-08, 03:40

Quote:

Originally Posted by Datanalysis

Do we need this made up petcolor_id primary key field in there?

excellent question, and the answer is absolutely not

the PK of the junction table should be a composite PK consisting of the two FKs

Datanalysis · #3 (**permalink**) 10-13-08, 11:09

Thank you for your response. Yes currently that is how I have the junction table 1 primary key with 2 foreign keys, so this correct? Or do I just need 2 foreign keys that would cross reference each other for pet and color?

r937 · #4 (**permalink**) 10-13-08, 14:53

the junction table will have only 2 columns

(i prefer to call it a relationship table)

each of the 2 columns is a FK

together, the two columns are the PK

Datanalysis · #5 (**permalink**) 10-13-08, 15:11

what happens if I leave that PK in there? when it comes to programming etc, more work

r937 · #6 (**permalink**) 10-13-08, 16:32

in that case you will need to define a separate UNIQUE constraint on the pair of FKs, to ensure that you don't link the same pet to the same colour more than once

gvee · #7 (**permalink**) 10-14-08, 03:46

And you'll also have remember to mark both columns as non nullable

...or this will be valid input

Code:

+------+------+------+
| skey | col1 | col2 |
+------+------+------+
| 1    | ABC  | DEF  |
| 2    | NULL | DEF  |
| 3    | ABC  | NULL |
| 4    | NULL | XYZ  |
| 5    | NULL | NULL |
+------+------+------+

Internet Services

Web Development

Digital Marketing

Consumer Tech