What are the pitfalls and advantages of using the following two database schemas

Question

Assuming you have a business that rents movie and you were designing a schema for your database. The customer will have to choose a plan, a delivery type, and a billingTerm (monthly, every three months, every six months). The plan has a base price and decides how many movies a customer can rent per month, and how many at once. The delivery type will change the price based on the customers choice (a regular delivery will add a certain value to the plan price, and expedited one will add a higher value. Would it be better to store the delivery type and billing terms information in the plans table, or in the customers table?

Assuming the following tables:

billingTerms(id, billEveryXMonths, discount)
deliveryTypes(id, type, price)

Approach one:

plans(id, name, price, billingTermsId, deliveryTypeId, monthlyLimit, atHomeLimit)
customers(id, name, planId)

This approach is much simpler, but essentially ends up duplicating the plans save for one attribute. The number of permutations and combinations is greater than having just two plans.

Approach two is to store plan id, delivery type, and billing terms in the customer table:

plans(id, name, price)
customers(id, name, planId, billingTermId, deliveryTypeId)

Additionally, the monthlyLimit and atHomeLimit will have to be linked to a controls table that keeps track of a customers activity each month.

Answer 1

You want to make your database schema as close to the "real world" as possible. So, in this case, since the delivery type and billing terms are associated with individual customers, NOT with plans, then you would include them in the customers table.

I'm not sure why'd you even want to consider putting the type and terms into the plan record, but you do say "this approach is much simpler". Could you elaborate on what you mean by that? To me, it seems like it would be much more confusing. Here's why:

Let's say you offer five different plans for your customer, then accordingly, you'd have five plans in your plan table. When you go to create any kind of data entry screen, you can just pull up your plan choices from the db and display them. If later you decide to add a new plan, you can just add it to the db, and if your view code is written correctly, it will just work.

However, if you were to include all the permutations in the plan table, then you can't use this approach, you'd have to hardcode the plans on your presentation screens, and then modify them when you added a new plan.

Also, if you separate the term and type from the plan, then you can create procedures to modify the customer that DO NOT ALSO have to modify the customer's choice of plan. This is naturally the right thing to do since the customer in this case has NOT modified their choice of plan.

Hope this makes sense.

Answer 2

What's the functional dependency for each attribute?

If each plan will always have the same billing term, then the billing term depends on plan, and should be an attribute of the plan table.

If each customer can have a different billing term, regardless of plan, then the billing term depends on the customer, and should be an attribute of the customer table.

However, if each plan has a default billing term but it can be overridden on a customer-by-customer basis, then there's a couple of choices:

1. Put billing term in each table, and have the queries using it perform a coalesce(cust.billing_term_id, plan.billing_term_id) to determine which is correct.

2. Have an associative entity Customer_X_Plan (customer_id (pk), plan_id (pk), billing_term_id) where you store the correct information about each customer's attributes that relate to their plan.

There's trade-offs in each; that's why it's design and not engineering.

Answer 3

plans and customers are distinct entities. As a customer I might want one plan for me, and another plan for my kids.

if you put the plan information in the customer table, I can't order two plans. And if I change plans you have no way of recording history.

normalize.

Answer 4

You haven't specified any keys or dependencies so this is all guesswork. I'd guess that in the Plan table the Id and/or plan name determines the price. Therefore I'd guess that the first model creates a non-key dependency, which is something you should try to avoid. Assuming Normal Form is your guide then I'd guess that the second option is the better design.

Maybe the reason you are having doubts is because you haven't identified the keys and dependencies in each case. It's generally a good idea to do that because ensuring your tables are in at least BCNF / 5th Normal Form will clear up a lot of design issues straight away.