i have two tables in my database which belongs to each other.
mp_order and mp_order_items.
mp_order has the main informations of an order of a customer like adress, date etc.
(order_id, customer_company, customer_name, customer_adress, order_date, … [etc.])
mp_order_items has the priducts/items which was ordered
(order_id, item_id, item_qty)
Due to order_id and item_id can repeat (but not in combination) i cant set one column as primary key.
Should i implement another column as unique identifier for the single entries or is it valid to have a table without primary key?
You have two options:
- Define a primary key on
- Define a synthetic primary key, such as an auto-incremented column.
I prefer the second method. It is more flexible for the future:
- Perhaps an order could contain the same items, but with different pricing or shipping addresses or shipping times.
- The rows are uniquely defined with a single number, which makes it easier to find them if you need to modify rows in the future.
- The rows are more easily referenced in another table, for instance, if you had a returns table or if the items.
Of course, having a composite primary key also works and is a very viable method for implementing the logic as well.