To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q013000-1.shtml

The SQL Guru Answers your Questions...


Today's question comes from Barry:

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

Barry,

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 and CarSpecial_Category. The 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 info about UNION in books online)

Hope this helps,
Sean


Article Information
Article Title: SQL Guru: Advice Needed on a Funky Data Model
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q013000-1.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers