I'm not sure how best to deal with the following.
Suppose I have an online movie rental store. Customers come to my website and rent DVDs. After renting a movie, they have the option of purchasing it.
Thus, there are two types of orders: rent and purchase orders.
Rent orders requires a shipping address; however, purchase orders do not.
#1) One solution is to create two separate sets of tables:
#2) Another solution is to dump everything into one set of tables.
Where order has the field "ordertype". When the shipping address fields are not needed, then they are set to NULL.
#3) Another solution is to create a third table:
Where order has two fields: orderID and ordertype
I think #2 is a bad solution because it does not scale with the addition of multiple orders. You will end up with a table with lots of fields. Also, you need to somehow keep track of which fields need to be set for which order type, which can become complicated and makes verifying database integrity more tedious and prone to error.
I'm not sure if there are any tangible benefits of doing #3 over #1. And I'm not sure if there exists other solutions which have eluded me. I would really appreciate advice on how to handle this sort of problem. Thanks in advance.
I would also need to store some billing information (name, address etc.) for the purchase. Also, I can't assume that the billing information used to make the purchase will be the same as that used for the rental.