Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am building a web based quoting system for a local automobile dealership.

I think I have figured out the logic for the databases but I want to get a second opinion before I start just to make sure I am doing it right.

I am working with the service plans for various vehicles and the client wants a drill down type search to find the required service plan e.g. Model of vehicle, transmission, etc...

The thing is the drill down is dynamic so some cars only have one type of transmission manual or automatic but with others you have to pick, the same applies to airbags and a few other things.

I was thinking I would have one table with the models and their specifications and then another table for each service plan at X number of KM's/Miles.

The table with models/specifications would populate the drop downs and I would then use this data to formulate a query to find the correct service plan table.

The reason for keeping the service plans in individual tables is because the data is so inconsistent.

Does this sound like the right approach?

share|improve this question
1  
No, it doesn't at first glance... any time you have more than one table per "thing" that's usually a bad sign. What do you specifically see as the advantage of this approach? What does it exactly solve? "The data is so inconsistent" is a little vague. –  Michael - sqlbot Jan 26 at 3:52
add comment

1 Answer

Start by thinking through a user interface. Will the user start with the make+model+year of the vehicle? Or will he start with "airbag"? More maybe you want to provide both ways to start the drilldown.

Don't plan to do all the work in the database. Plan to either have some application code to sort through the "so inconsistent" data, or throw text at the user, and let him sift through the final information.

How many different sets of info do you have? One per make+model+year? One per component with a service plan?

The design might be one table, with lots of columns.

Or it may need 3 tables (to start with). One for each distinctly different vehicle; perhaps PRIMARY KEY (make, model, year). One for service plans; this would contain an id, a TEXT field, and some meta information ('airbag', mfg's id, etc). The third would be the "relationship" that ties the two other tables in a many-to-many relationship; it would have two ids (for JOINing to the other tables).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.