How do I resolve this situation with many to many

Datanalysis · #1 (**permalink**) 10-29-08, 00:00

Hello, database is on a pet shop, heres my format on three tables that are related on the ERD our rule is assigning 3 main colors to 1 pet for identification. It is a many-to-many relationship between pet and colors.

Pet table
---------
pet_id PK

Composite table
-----------
pet_id FK
color_id FK

Color table
-----------
color_id PK
color desc

Is this right? or should I have in the composite table pet_id FK and 3 FK color_id fields that would have each a color referencing the color table? Appreciate your help.

gvee · #2 (**permalink**) 10-29-08, 04:41

What you've described so far appears AOK to me

Datanalysis · #3 (**permalink**) 10-31-08, 13:35

How would I go about coding the composite table?

loquin · #4 (**permalink**) 11-03-08, 17:49

Something along the lines of ...

Code:

Create Table PetColor
  (ColumnA  ColumnType Not Null,
   ColumnB  ColumnType Not Null)

add foreign key relationships to ColumnA and ColumnB to reference your two MANY tables, and your compound primary key.

ref http://www.1keydata.com/sql/sqlcreate.html

r937 · #5 (**permalink**) 11-03-08, 18:11

Code:

CREATE TABLE petcolours
( pet_id      INTEGER NOT NULL 
, colour_id   INTEGER NOT NULL 
, CONSTRAINT pet_fk
    FOREIGN KEY ( pet_id ) REFERENCES pets ( pet_id )
, CONSTRAINT colour_fk
    FOREIGN KEY ( colour_id ) REFERENCES colours ( colour_id )
, PRIMARY KEY ( pet_id, colour_id )
);

Internet Services

Web Development

Digital Marketing

Consumer Tech