Fri, 2004-04-23 03:50
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 
So .. help me out here 
Fri, 2004-04-23 04:07
#1
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
Fri, 2004-04-23 08:24
#2
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 
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.
Fri, 2004-04-23 10:39
#3
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
Fri, 2004-04-23 12:52
#4
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.