Is it okay to have a LOT of null values in MySQL Database?

Question

I'm not sure how to word this perfectly, but let me give it a try. I am working on building an inventory control application to be used with many different customers to do everything from check their own stock of items, get prices, order product, etc. The front-end is in Flex (Flash Builder), and the back end is MySQL and PHP. So - here's the real question I guess. When the customer places an order for product I am not sure how many of what they are ordering, but I need to save all items onto one 'ticket'. (Example: One order might be for 2 apples, 3 oranges, a banana, and a kiwi - The next might just order one apple.) So in my 'tickets' database I have space in there for up to 20 items (ticketItem1, ticketItem2, etc...) - The obvious drawback here is that if the customer only orders one product I am left with 19 empty spaces.

What types of problems, if any, are normally associated with having a lot of NULL values in a database? Is there a way to help prevent them from happening? And is there any suggestions to help aid in this?

Also and finally - Each item ordered (let's use an apple again) has it's own UNIQUE bar code associated with it. So one apple might be numbered 0001, and another may be 4524...

Thank you for any assistance you can offer.

-CS

Answer 1

Instead of having 20 columns ticketItem1, ticketItem2, ..., introduce a table order_item, like this:

Table order:

id customer
1  42
2  23

Table item:

id name
1  banana
2  kiwi
3  apple
4  oranges

Table order_item:

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 an order.

Answer 2

You should have a table that lists the Order, and one for the OrderDetails with an OrderID pointing to the Order table

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