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

Read Other SQL Guru Questions