Today's question comes from Barry:
I am displaying a list of Cars on my website. I have 3 basic tables:
CategoryDescription, etc...). An example of some categories are Luxury, Sport Utility, Mini-Van, etc...
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
I'm thinking of modeling the database this way: adding a new table
CarAvailability, etc...) with
CarDescriptionall being a nullable fields (as they could potentially be filled in already in the
Carstable). If that car exists already then only fill in the
CarIDthat relates back to the
CarsTable. Otherwise, leave the
CarIDblank and fill in the other info.
The SQL to list Car Specials could be a left join between the
CarSpecialstable and the
Carstable (with the join on the
It sounds kind've funky and disjointed. Is there a better way to do it? Also, if the
CarIDis nullable in the
CarSpecialstable then how would I indicate the Cars' category??
Hmm, I agree that the solution you've proposed is a bit "funky"... I can think of a couple of others that might be a bit cleaner.
The cleanest solution would be to just add the special cars into the Car table. If necessary, you could add some sort of flag to distinguish between cars in the showroom and cars not in the showroom.
If for some reason you can't add special cars into the
Car table, you could
create another set of tables, say,
CarSpecial table would only contain cars not already in the
Car table. You
could then use a couple of
SELECT statements, one for normal cars, and one
for special cars, and
UNION them together. Keep in mind that both select
statements will need to have identical column lists. (You can find other
UNION in books online)
Hope this helps,