Option 1 is close...
However:
If a student can own zero to many cars:
- you'd have
ownerstudentid
in the car table
- car is a child of student
If a car can exist without a student, I'd consider a many-many table carstudent
with a constraint on carid to allow zero or one owning student
Note:
This is where tools like NORMA come in useful to capture these relationships and constraints in plain English. See Object Role Modelling
In a transactional system you will want to record the values that applied to the transaction at the time it happened.
It is a mistake to try to use relationships to connect a transaction to a date-depenendent lookup data. The reason for this is that changes to the date-dependent data that happen after the transaction will restate what should have happened to the transaction. This will cause confusion and errors.
The way to handle this type of situation is to denormalize the actual value applied to the payment transaction. Your model doesn't reflect this, but if you had named categories for your discounts and timespan rates, you could keep a relationship from the transaction to the category name while still denormalizing the actual (time sensitive) rate/percent.
EDIT: What should the logical model look like?
In your logical model, the issue is that you need multiple discount (or price) rates because they change over time - or just because there may be multiple rules, for example one price for regular customers and one for special customers, etc.
What is missing is the notion of a rate table. This is like a header which then has rate details attached to it. What you have in your model now is really just the rate detail. You need to insert the rate table header in between the transaction and the rate detail. In your logical model, the relationship is between the transaction and the rate table header. This is OK because the rate table is more stable than the details which change over time.
In your physical model you still need to denormalize make a point in time snapshot of the actual rate used into your transaction because that is the only reliable way to make sure that editing the rates won't restate history.
Best Answer
So you have several tables each of which holds an address. You're wondering if these common address-related columns should be in their own table.
The typical answer is "yes" - a separate would be a good idea. There are any number of posts on this subject on this site.
If you do make a common address table will you compare user input to existing rows so you can re-use an existing address_id in one of your tables? If the answer is "no", or you don't have a plan to handle small variations in how the user spells or formats the address, then why bother?