Mysql – Serialized objects vs multiple tables

database-designinnodbMySQL

What would be the best way to store several entries in a database, each containing a list of other entries? An example of this would be a table of receipts, where each receipt has some information that's always present, such as time and the total amount. In addition to this, each receipt would have a variable-length list of entries, each one consisting of a descriptive text and a price.

I would be using a mySQL database to store the information and PHP to access it.

Best Answer

This is a typical 1 to many relationship (or parent child relationship). The parent would have the fixed infromation and the child would have the many varying information. The key of the parent must appear in the child. Example:

Receipt : {ReciptID, Date} RecieptItem : {ItemID, CustomerPrice, Quantity,TotalPrice,ReceiptID, Status, Date} Item:{ItemID, ItemName, Price}

Now each Recipt can have 0,1 or more Recipt Items. Each Item is references on 0,1, or more ReciptItems.

The column ReceiptID in ReceiptItem is referred to as a Foreign Key. A constraint could be built to make sure that every ReciptID in ReciptItem corresponds to a ReceiptID in parent Receipt table.

Example ERD: enter image description here

Related Question