Hello,
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:
{rentorder, rentorder2movie}
{purchaseorder, purchaseorder2movie}

#2) Another solution is to dump everything into one set of tables.
{order, order2movie}
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:
{order}
{rentorder, rentorder2movie}
{purchaseorder, purchaseorder2movie}

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.

Since they have to rent the movie before they purchase it why not just have one order table and add a flag in the order2movie table that indicates which of the movies they (eventually) purchased?

Order:
287 - Jim Smith, 100 North Main
Movie:
888 - Bad Movie1
889 - Bad Movie2
Order2Movie:
Order 287, Movie 888, Purchase N
Order 287, Movie 889, Purchase Y

Hello MarkATrombley, thank you.

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.

Then your best option is #2. If you use varchar columns the storage space taken up by nulls will be insignificant, and having less tables to deal with will make the programming easier.