Is it enough to have many null values ​​in a MySQL database?

Question

I'm not sure how to say it right, but let me try. I am working on creating an inventory management application that will be used with many different customers to do everything to check their own stock of items, get prices, order goods, etc. The interface is in Flex (Flash Builder) and back end is MySQL and PHP. So - here is the real question, I think. When a customer places an order for a product, I’m not sure how much of what they order, but I need to save all the items on one “ticket”. (Example: one order can be for two apples, 3 oranges, bananas and kiwi. The next can just order one apple.) So in my “tickets” database I have space for up to 20 items (ticketItem1, ticketItem2, etc. d.). The obvious drawback here is that if a customer orders only one product, I am left with 19 empty spaces.

What types of problems, if any, are usually associated with a large number of NULL values ​​in the database? Is there any way to help prevent them? And are there any suggestions to help with this?

And finally, each ordered element (even if it uses the kernel again) has its own UNIQUE barcode associated with it. Thus, one apple can be numbered 0001, and the other can be 4524 ...

Thank you for any help you can offer.

-CS

Answer 1

Instead of the 20 columns ticketItem1, ticketItem2, ... enter the order_item table, for example:

Table order :

 id customer 1 42 2 23 

Item table:

 id name 1 banana 2 kiwi 3 apple 4 oranges 

Order_item table:

 order_id item_id multiplicity 1 1 1 # 1 banana 1 2 1 # 1 kiwi 1 3 2 # 2 apples 1 4 3 # 3 oranges 2 3 1 # 1 apple in the second order 

Then JOIN over the tables to get all ordered items in order.

Answer 2

You should have a table with an order list and one for OrderDetails with the orderID identifier pointing to the Order table

So, if you have 10 products in order. You will have 1 row in order and 10 in OrderDetails

Answer 3

If you want to be dynamic in what you store, you can use a json array. I completely agree with others that normalizing a database is the first thing to do. I will follow the rules of Codd.

But if you do not know exactly what you need to store json, perhaps this is the solution. However, one major drawback is that the search becomes quite complex.