|
Mr. Guru,
I am displaying a list of Cars on my website. I have 3 basic tables:
Car - CarID, CarName, CarDescription, etc...
Category - CategoryID, CategoryName, CategoryDescription,
etc...). An example of some categories are Luxury, Sport Utility,
Mini-Van, etc...
Car_Category - CarID, CategoryID.
This represents a many-to-many relationship between a car and a category
(after all, a car can have more than one category - it can be a Mini-van
and a Luxury Car).
To List the cars and their categories I use a standard SQL query:
SELECT Car.CarName, Category.CategoryName
FROM Car, Category, Car_Category
WHERE Car.CarID = Car_Category.CarID
And Category.CategoryID = Car_Category.CategoryID
|
Now here is the problem... At times, I have what is known as Car
Specials which is where a car is available at a discount in a special
place for a short period of time. I want to list these car specials as
well. The issue is that sometimes these cars are already in the Cars
table and other times these are just cars pulled out of nowhere not in
our standard showroom (and thus not in our Cars Table).
I'm thinking of modeling the database this way: adding a new table
CarSpecials (CarID, CarName,
CarDescription, CarDiscount,
CarAvailability, etc...) with CarID,
CarName, and CarDescription all
being a nullable fields (as they could potentially be filled in already
in the Cars table). If that car exists already then only fill in the
CarID that relates back to the Cars Table. Otherwise, leave the
CarID
blank and fill in the other info.
The SQL to list Car Specials could be a left join between the
CarSpecials table and the Cars
table (with the join on the CarID field).
It sounds kind've funky and disjointed. Is there a better way to do it?
Also, if the CarID is nullable in the CarSpecials table then how would I
indicate the Cars' category??
Thanks,
Barry
|
