4 replies [Last post]
Fruitcake
Offline
Enthusiast
Perth, Australia
Last seen: 13 years 2 weeks ago
Perth, Australia
Timezone: GMT+8
Joined: 2004-04-12
Posts: 257
Points: 0

I'm writing a shopping cart database for the first time in MySQL.
I figured that one order can have many products and one product can be in many orders therefore creating a many-many relationship. Should i create i composite primary key in the new table i must create (which i figured will be Order_id and Line_no in an Invoice Lines table) in the database? Or should i just rely on the query to pick where order_id = whatever?
Just not sure if MySQL will like me having 2 priomary keys. I know that Microsoft Access has a sad if you don't have a unique value as a primary key Oups

So .. help me out here Laughing out loud

I am Dan, Dan I am.

Tags:
Tony
Tony's picture
Offline
Moderator
Brisbane
Last seen: 1 day 6 hours ago
Brisbane
Timezone: GMT+10
Joined: 2003-03-12
Posts: 5343
Points: 2964

Database Design - Composite Primary Key

Hi Fruitcake,
Having unique primary keys is important.
Sounds like you should need at least three tables:
customers:
which would contain a unique id for each customer and contact details etc.

product:
contains details of the product such as a unique id description price etc.

and orders:
this one matches the customer to the product.
You need to store the primary key of the customer with the primary key of the product as well as the quantity etc.

Hope that helps

Fruitcake
Offline
Enthusiast
Perth, Australia
Last seen: 13 years 2 weeks ago
Perth, Australia
Timezone: GMT+8
Joined: 2004-04-12
Posts: 257
Points: 0

Database Design - Composite Primary Key

Here is a picture of what i have so far. I did this quick version in microsoft access. You will see that in the InvoiceLines table that i have a composite primary key being the order_id and line number. That proves to be unique as for order_id 1 will have line numbers 1,2,3,... but you will never have line number 1 twice in the same order_id. If you know what i'm getting at :roll:
I'm not that good at describing things unfortunately Tongue

What i'm wondering is should/can i in a MySQL database make a composite primary key like that or should i make a dummy primary key?

NOTE: This is not the final draft of it, just a quick demo of what i mean.

I am Dan, Dan I am.

Tony
Tony's picture
Offline
Moderator
Brisbane
Last seen: 1 day 6 hours ago
Brisbane
Timezone: GMT+10
Joined: 2003-03-12
Posts: 5343
Points: 2964

Database Design - Composite Primary Key

Hi Fruitcake,
Composite keys should be fine.

Keep in mind that MySQL doesn't enforce referential entegrity, so you can have a foreign keys as long as you enforce them in your code.

Keep things as simple as you can it will make life easier later.

Hope that helps

Fruitcake
Offline
Enthusiast
Perth, Australia
Last seen: 13 years 2 weeks ago
Perth, Australia
Timezone: GMT+8
Joined: 2004-04-12
Posts: 257
Points: 0

Database Design - Composite Primary Key

OK thanks.

I think i'll remove the line_no and perhaps add a dummy field for a primary key for good measure and the rest i'll just do in my code.

I am Dan, Dan I am.