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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

closed as unclear what you're asking by Max Vernon, Colin 't Hart, RolandoMySQLDBA, RLF, dezso Feb 3 '15 at 9:50

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

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 '14 at 3:52

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

I agree with Rick and I Michaels answers, and would go further. In my opinion you are talking about the difference between tables and the interface.

I would question if you need to have a schedule table at all. I'd store each vehicle's details and service requirements. Personally id have different tables for make/model and the details of the items that need to be serviced and at what distances/intervals.

I would have a form that looks up the make/model and works out what needs to be serviced depending upon the mileage. You need the details related to that specific car and component. Why not store them together?

Unless you need to store the details of jobs done why bother storing all of the possible iterations of service schedule? Can 1 schedule be reused for many cars?

Another thing to consider is where you will get all of this data from. Is it in a format that can be easily imported? Text data? Microfiche? Without data your system isn't going to be much use. And hundreds of hours of data entry is boring and expensive. Do you need to the data in database fields so you can perform calculations and queries on it? Or does the user just need to be able to lookup the details. (Ie pdf/microfiche )

If you haven't done so already i suggest that you look at a few different service manuals for different cars. You may have some different ideas after that.

share|improve this answer
The more I think about it, the more I think you need to clarify the requirement. What exactly is your system trying to do. What data does it need to do that. Where will that data come from. And how will you load it and use it? Will it require formatting and conversion or data entry. If you can answer those questions, I think you'll be most of the way to answering your design questions. Not because it's the best way of doing it, but because it is "do-able". – Peter Jan 31 '15 at 9:21

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