When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles [1.x] [2.0]
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
Headlines from ASPWire.com
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML Info
Information:
Advertise
Feedback
Author an Article
Technology Jobs

















internet.com
IT
Developer
Internet News
Small Business
Personal Technology
International

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
Print this page.

Windows Systems Administrator
Jupitermedia
US-CT-Darien

Justtechjobs.com Post A Job | Post A Resume

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


Read Other SQL Guru Questions


Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article



JupiterOnlineMedia

internet.comearthweb.comDevx.commediabistro.comGraphics.com

Search:

Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

Jupitermedia Corporate Info


Legal Notices, Licensing, Reprints, & Permissions, Privacy Policy.

Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers

Solutions
Whitepapers and eBooks
Microsoft Article: HyperV-The Killer Feature in WinServer ‘08
Avaya Article: How to Feed Data into the Avaya Event Processor
Microsoft Article: Install What You Need with Win Server ‘08
HP eBook: Putting the Green into IT
Whitepaper: HP Integrated Citrix XenServer for HP ProLiant Servers
Intel Go Parallel Portal: Interview with C++ Guru Herb Sutter, Part 1
Intel Go Parallel Portal: Interview with C++ Guru Herb Sutter, Part 2--The Future of Concurrency
Avaya Article: Setting Up a SIP A/S Development Environment
IBM Article: How Cool Is Your Data Center?
Microsoft Article: Managing Virtual Machines with Microsoft System Center
HP eBook: Storage Networking , Part 1
Microsoft Article: Solving Data Center Complexity with Microsoft System Center Configuration Manager 2007
MORE WHITEPAPERS, EBOOKS, AND ARTICLES
Webcasts
Intel Video: Are Multi-core Processors Here to Stay?
On-Demand Webcast: Five Virtualization Trends to Watch
HP Video: Page Cost Calculator
Intel Video: APIs for Parallel Programming
HP Webcast: Storage Is Changing Fast - Be Ready or Be Left Behind
Microsoft Silverlight Video: Creating Fading Controls with Expression Design and Expression Blend 2
MORE WEBCASTS, PODCASTS, AND VIDEOS
Downloads and eKits
Sun Download: Solaris 8 Migration Assistant
Sybase Download: SQL Anywhere Developer Edition
Red Gate Download: SQL Backup Pro and free DBA Best Practices eBook
Red Gate Download: SQL Compare Pro 6
Iron Speed Designer Application Generator
MORE DOWNLOADS, EKITS, AND FREE TRIALS
Tutorials and Demos
How-to-Article: Preparing for Hyper-Threading Technology and Dual Core Technology
eTouch PDF: Conquering the Tyranny of E-Mail and Word Processors
IBM Article: Collaborating in the High-Performance Workplace
HP Demo: StorageWorks EVA4400
Intel Featured Algorhythm: Intel Threading Building Blocks--The Pipeline Class
Microsoft How-to Article: Get Going with Silverlight and Windows Live
MORE TUTORIALS, DEMOS AND STEP-BY-STEP GUIDES