CODEWITHSUNDEEP
sqldatabasedatabase-design
Ben
Ben

Reputation: 21625

How do I properly design a database schema for this scenario

Suppose I owned a business that sold cars and motorcycles. I want to track data on each. My company provides a free warranty for every car, but we do not even offer warranties on motorcycles. Initially, I thought I'd have two tables - a "Vehicles" table and a "Warranties" table, like this

Vehicles                                                     Warranties

VehicleID, SalePrice, VehicleType, WarrantyID                WarrantyID, EffDate, ExpDate

Where VehicleType is either "car" or "motorcycle". My drawback to this is that, in the Vehicles table, every motorcycle would have a null value for "WarrantyID". Would this be considered bad practice?

Another approach I've considered is using three tables like

Cars                                                         Motorcycles

VehicleID, SalePrice, WarrantyID                             Vehicle ID, SalePrice


Warranties

WarrantyID, EffDate, ExpDate

My drawback to this is that I'm separating motorcycles and cars into two tables that will almost be identical. (In reality, they'll have more fields like purchase cost, mileage, etc). The ONLY difference is that all cars will have a warranty and no motorcycles will have a warranty.

(Note: I'm also making the assumption that 2 or 3 cars could share a single warranty.)

What's the proper way to set up this database?

Upvotes: 2

Views: 1306

Answers (5)

buritos
buritos

Reputation: 598

If warranty is optional for some vehicles and your assumption is not a requirement, you could put the FK in the Warranty table. The following also assumes that there is a one to one relationship between Vehicle and Warranty (if it exists).

Vehicle              Warranty
---------            ---------------
VehicleId            VehicleId
SalePrice            EffectiveDate

the drawback is that you would have to pay the price of an outer join or query for Warranty in order to find out if the vehicle came with one or not.

Its hard to tell what's best without knowing your query pattern.

Upvotes: 0

Antonio Sánchez-Padial
Antonio Sánchez-Padial

Reputation: 186

It depends on the use you're going to do on the tables.

If you will query lists of mixed vehicles then you should go for the first model.

But if you're not going to mix them you could use the second.

I would choose the first as it will let you to offer warranty for bikes in the future.

Upvotes: 1

Jens Schauder
Jens Schauder

Reputation: 81907

There is no proper way, both approaches are valid. It depends what the business is all about.

Is it about Vehicles, i.e. a lot of stuff in the application deals with Vehicles, without caring if it is a Car or a Motorcycle, then you probably want one table.

But it is perfectly possible have separate tables, possibly with a view combining both, if most of the business deals with either one or the other, but not both.

Upvotes: 1

Marvo
Marvo

Reputation: 18143

Sounds like you could consider a join table.

Vehicle              VehicleWarranty          Warranty
---------            ---------------          ----------
VehicleId            VehicleId                WarrantyId
SalePrice            WarrantyId               EffectiveDate

That way, there's no Warranty ID in your Vehicle table, so you don't have to deal with nulls. There's only an entry in the Warranty table (and the VehicleWarranty table) if you have a warranty on a vehicle. Further, the join table allows you to attach the same warranty to multiple vehicles, or the same car to multiple warranties.

Upvotes: 5

Bill Karwin
Bill Karwin

Reputation: 562310

Store the motorcycles and cars in the same table.

It's totally ordinary to allow a column to be NULL when the attribute does not pertain to the type of data in a given row. NULL is for "unknown, missing, or inapplicable data."

Upvotes: 6

Related Questions