• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

New database project | Database structure help

I have been asked to create a website for a company that sells trailer hitches and all related accessories.
They carry several manufacturers' hitches.
I'll need to create a hitch finder that will prompt the user for the year, make and model of the vehicle.

I then need to display all hitches from all manufacturers for that vehichle and display links to see wiring harnesses, and related accessories for that vehicvle that will not require a new search.

The database must also be structured so that an Excel file can be used to upload new data (with the exception of images).

Do I need a table for years, and table for makes and a table for models?  Even if I do, how do I link that data to a table of hitches?  Once this is figured out, I'm sure the same schema can be applied to wiring harnesses, etc.

I am struggling with how to design the required table(s) needed for this project.  I do not know where to start.

Any suggestions?
3 Solutions
Keep it simple?  As much as I hate redundant data in tables a base point may be

Vehicle table, stick everything vehicle related (year, make, model).  If you want to create year, make and model tables related to the vehicle table ok, and makes the db smaller, but a bit more work to maintain (a couple more forms).

Then a hitch header table, manufacture, links, contact, etc.
Then a hitch detail table where you store the pk hitch and pk vehicle.

When user selects year, make, model you return the pk from vehicle table and then query the hitch detail to return all corresponding hitches.

SELECT hitchHead.* FROM hitchHead INNER JOIN hitchDetail ON hitchPK=hitchFK WHERE hitchDetail.vehicleFK IN(SELECT vehiclePK FROM vehicles WHERE year=@year AND make=@make AND model=@model)

You could do SELECT DISTINCT year, etc. FROM vehicle for your dropdownlist or go the extra mile and build those year, make and model tables and join them to the vehicle table.

Just a thought as well...
For the harnesses same idea but have a harnessHeader for the links, etc. then a harnessDetail for the harnessFK, hitchFK and vehicleFK.
Think of tables as: (normalization - you need to consider it seriously)

Parents (pID, pName, otherfields related to parent)
Children(cID, pID, cName, other fields related to child)

Your data entry fills a parent's record, then fills the child's record.

You can join the 2 tables to display info of any childfrom Children table, and extra info from his father in Parents table joined with equal pID.
Ray PaseurCommented:
The Excel part of this sounds like something that would be needed at the time of initial loading, but going forward, the client should be able to use a "table maintenance" script to keep things lined up.

I would probably organize it this way:  One table for vehicles, one table for hitches, one table for harnesses (and one new table for each new product they add to the portfolio).  Then I would create a junction table providing the logical linkage between the vehicles and hitches, and between the vehicles and harnesses.  In this design I am assuming that the harness is dependent on the vehicle.  If the harness is dependent on the hitch then the junction table would link the hitch and harness, rather than the vehicle and harness.  It's quite possible that the harness would be dependent on both the vehicle and the hitch, and if this is the case, the junction table would link all three of vehicle and hitch and harness.

Some pidgin-code... the *id columns are primary AUTO_INCREMENT keys:
TABLE vehicle (vid, year, make, model)
TABLE hitch (hid, additional hitchdata cols)
TABLE wiring (wid, additional wiringdata cols)

Junction tables might look like this:
TABLE vehicle_and_hitch(id, vid, hid)
TABLE vehicle_and_wiring(id, vid, wid)

Or if there are more restrictive relationships a junction table might look like this:
TABLE vehicle_and_hitch_and_wiring (id, vid, hid, wid)

Your query WHERE clauses should be pretty obvious from this table structure.  Your relational tables (junction tables) will have a lot of rows, but the lookups can be very fast because all of the columns are of type INT and you can index all of the columns.  To find all of the hitches that fit a 2010 Ford Escape your query might look like this:

SELECT vehicle.vid, hitch.hid, hitchdata
FROM vehicle, hitch, vehicle_and_hitch
WHERE year='2010'
AND make='Ford'
AND model='Escape'
AND vehicle.vid = vehicle_and_hitch.vid
AND hitch.hid = vehicle_and_hitch.hid
GROUP BY hitch.hid
ORDER BY (whatever)
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

dimmergeekAuthor Commented:
@Ray_Paseur, your proposal is interesting, but looks like a lot of maintenance going forward.  Am I better off just doing a join in my queries as items are selected?  Also, the client will be uploading items in mass, not one at a time...usually as new model years come out.  SUre there will be onsie twosies and I will certainly have table maintenance pages for that.  However, when the vendors come out with their 2014 model year items, for example, they are asking for the ability to doa bulk insert.
Ray PaseurCommented:
Am I better off just doing a join in my queries as items are selected?
Good question, and I'm not sure there would be any difference in the resulting output.  As long as you have relational tables that coordinate all of the appropriately fitted parts you should be OK.
dimmergeekAuthor Commented:
I'm sorry for the delay in response.  I have been out of work the past couple weekdays with my daughter after she had to undergo some surgery.
Ray PaseurCommented:
NP, I certainly understand.  I hope your daughter is coming along well.
Ray PaseurCommented:
Thanks for the points and best of luck with the project, ~Ray
dimmergeekAuthor Commented:
Thanks, Ray!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now