3

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.

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.