I have been trying to design a MySQL table o store the items of the store purchased by the costumers. I am stuck with what approach should I take to design a good table.

My first option is:

In this approach, I'll create may be 20 columns for items (assuming that a costumer may buy a maximum of 20 items at a time). ID of the items will be stored in the item(n)_id columns for that specific bill_id_fk. My concern with this approach is that it would be difficult to query later for a specific item, like how many times a specific item has been sold.

My second opinion is:

In this approach, I'll just create 3 columns and for each item I'll create a rows with the bill_id_fk for a specific bill. In this approach, it is easier to query for a counts of the sell of a specific item. But my concern is creating thousands and thousands of rows when the app will be used and how will that affect the performance of the app over time?

I'd like to have your opinion on what is the best practice for designing such database. Or is there any other approach should I take?

CodePudding user response：

There's no chance that you will go with the first choice, the second is the best approach for your case.

it will not affect your performance if you indexed the right columns.

When it comes to items can add a column to your bills table that holds item numbers, for example:

bills (id - total_price - user_id - item_counts)
bill_items (id - bill_id - item_id - item_price)