Check my format on tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Check my format on tables

Author

Topic

Jamboo
Starting Member

3 Posts

Posted - 2008-10-28 : 23:54:39

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 and 3 FK color fields that would have each a color referencing the color table? Appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 23:57:32

Nope. this design looks fine. you just need only pet_id and color_id in composite table.

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-29 : 00:26:57

Thats right.for each relation between pet_id and color, compsite table is enough.

malay

onlyforme
Starting Member

25 Posts

Posted - 2008-10-29 : 00:41:05

Relation is correct,but u need only composite table to add color_id and color

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 00:46:40

what do you mean by that? add colorid & color to composite table and drop color table altogether?then where will you represent those colors which are not yet linked to any existing pet?

Jamboo
Starting Member

3 Posts

Posted - 2008-10-31 : 13:26:26

Do I have to set a composite primary key or a composite foreign key for the petcolor (composite) table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 13:29:00

you can have an identity column in petcolor table (may be petcolrid) as primary key. also set foreign key relationships as colorid--->color(colorid) and petid--->pet(petid)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources