CODEWITHSUNDEEP
sqldatabase-design
patrick mc carrick
patrick mc carrick

Reputation: 63

SQL multiple values, table design

This is more of a question about structure more than the programming language itself. I have a very basic knowledge of SQL.

If you have a table with products and each product can have multiple or no colors.

Table: Color
Columns: Id, ColorName

1, Black
2, White  
3, Purple  
4, Red

and then we have 

Table: Phone
Columns: Id, PhoneName, PhoneManf, ColorID

eg. Phone Table could have a HTC EVO that is available in black and white

would this mean that for every color of every phone there would be a record. i.e. 

1, EVO, HTC, 1
2, EVO, HTC, 2
3, Curve, Blackberry,1
4, Curve, Blackberry,3

Or is there a way to have 1 record for each phone where the color key is like an array ?

Sorry if this is a very basic question, I am newish to this and want to make sure I am starting off right.

Upvotes: 3

Views: 761

Answers (5)

jerrygarciuh
jerrygarciuh

Reputation: 21998

What I would do is create a bridging table called phone_color with one column for phone and another for color. These columns store the respective ids mapping the many-to-many relationship.

So given the data above your rows in the bridging table would be

1,1
2,2
3,1
4,3

Hope that helps.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Simple normalization

Table: Color
Columns: ColorId, ColorName
Primary Key: ColorId

// table for the many-to-many relationship
// between Colors and Phones
Table: PhoneColor
Columns: PhoneId, ColorId
Primary Key: (PhoneId, ColorId)
Foreign Key: PhoneId
Foreign Key: ColorId

Table: Phone
Columns: PhoneId, PhoneName, PhoneManfId
Primary Key: PhoneId
Foreign Key: PhoneManfId

Table: PhoneManf
Columns: PhoneManfId, PhoneManfName, PhoneManfAddress
Primary Key: PhoneManfId

Any other details you have regarding phones, like SKU should go to table Phone (or Model if you want to call it that way).

Details for Manufacturers should go to table PhoneManf, etc.

Upvotes: 2

servermanfail
servermanfail

Reputation: 2538

You need a many-many relationship. You need a 3rd "mapping" table called PhoneColor. See http://www.tonymarston.net/php-mysql/many-to-many.html for more insight.

Upvotes: 3

mdrg
mdrg

Reputation: 3402

Duplicating essential data (like phone model and manufacturer) just because it is available under other colors is unecessary redundancy.

Better create a table like phone_color (IdPhone, IdColor)

Upvotes: 4

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120937

You can just create another table:

Table: PhoneColor
Columns: PhoneId, ColorId

Upvotes: 2

Related Questions