Unless you have a good reason not to, you should normalize your database schema. When you normalize your database schema you are avoiding potential issues with data consistency that can occur when you have duplicated data.
Google the terms: insert anomaly, update anomaly, deletion anomaly. You will see lots of examples of the kinds of problems that an unnormalized database can cause.
Regarding modeling CONTRACT
separately from CAR
: I have found that one of the best places to start with a data model is to treat each tangible (or significant intangible) thing that your system cares about as its own table.
The hard reality is that business rules change, but the things that your business cares about don't change - at least not at nearly the same pace. Therefore, you want your data to reflect the reality of the things your business cares about.
People are not contracts. Sure, today your boss says one contract per person per car. What happens when your boss buys (or builds) a new parking garage and that car has a contract for both? What happens when one person sells a car that has a contract? The contract is signed by a person, not by the car? Does the contract go with the car or does it stay with the owner? What happens when your boss tells you he wants to start tracking future-dated contracts so that a car can have both its current contract and its contract for next year?
You don't have to normalize. You could try to get away with one big table for everything. You could just write everything down in a spreadsheet or on a piece of paper. However, if you want to build a system that is flexible for the future, then start by normalizing.
Regarding IDs for your tables: Never use anything external as the ID for something, unless you are totally positive that it will never change. A vehicle VIN is OK as an identifier. Nothing causes a VIN to change and it is guaranteed to be unique. A passport number is a terrible ID because (a) it will definitely change as time goes on and (b) you can't be sure that it will be unique. When primary keys change it causes all kinds of headaches. This is why a lot of people assign internal, meaningless surrogate keys to their tables.
EDIT: Something Else to Consider:
Something else you should consider is that you should NOT try to model (and maintain data for) things which aren't important to your system.
That is just making work for yourself now and down the road. Your system will be more complicated to build, maintain, upgrade and use.
Consider the following ERD:
The relationship between CAR
and OWNER
is not important to your system! At first this seems a little bit counterintuitive, but think about this: Does your boss care who actually owns a car? No! What they care about is who (PERSON) signed a contract to pay to have which CAR parked in his garage. Therefore the relationships that are actually important are between CONTRACT and CAR, and CONTRACT and OWNER. The relationship between CAR and OWNER is not directly significant to your system, unless you have a business rule somewhere that says something about how contracts stay with cars, not owners when people sell their cars. That is probably dicey from a legal perspective.
I think you can, using a "diamond" relationship diagram:
CREATE TABLE Artist
( artistID INT NOT NULL
, name VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID)
) ;
CREATE TABLE Album
( artistID INT NOT NULL
, albumID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, albumID)
, FOREIGN KEY (artistID)
REFERENCES Artist (artistID)
) ;
CREATE TABLE Track
( artistID INT NOT NULL
, trackID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, trackID)
, FOREIGN KEY (artistID)
REFERENCES Artist (artistID)
) ;
CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (albumID, trackNo)
, FOREIGN KEY (artistID, albumID)
REFERENCES Album (artistID, albumID)
, FOREIGN KEY (artistID, trackID)
REFERENCES Track (artistID, trackID)
, UNIQUE (trackID, albumID) -- this Unique constraint should be added
-- if no track is allowed twice in an album
) ;
Best Answer
The primary key is a way to distinguish one row in a single table from all other rows in that same table. It is not a way to distinguish one row in the context of its associated rows from other tables.
Sometimes a table's primary key consists of a single column. A person's user_id would be an example.
Sometimes it is made up of several columns. A location is both latitude and longitude. This is known as a compound key. Sometimes one or more of those columns may also be a foreign key. This is termed a weak entity type.
To take your example - could a single row in the Orders table be distinguished from all other rows by the Order Number alone? Typically, yes. The order number is unique across the whole system. So given order number 8765 we know that's for customer A. This makes Order a strong entity type.
How about the OrderLine table? Given a single order line number, say "1", could we unambiguously find which Order that relates to? Typically no, because order line numbers start again for each Order. OrderLine is therefore a weak entity because its primary key (order number, order line number) requires the primary key from another related table, viz. Order.
So according to the business rules it makes no sense for an Order to exist without the Customer but according to the database rules this is OK. An OrderLine cannot exist without the Order under either set of rules.