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 only takes a minute to sign up.
Sign up to join this community
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.
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:
