Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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