New database project | Database structure help

Posted on 2013-11-08
Last Modified: 2013-11-27
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?
Question by:dimmergeek
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

FriedTyGuy earned 167 total points
ID: 39634913
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.
LVL 30

Assisted Solution

hnasr earned 167 total points
ID: 39635069
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.
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 166 total points
ID: 39638796
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)
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 39642033
@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.
LVL 110

Expert Comment

by:Ray Paseur
ID: 39642281
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.

Author Comment

ID: 39656393
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.
LVL 110

Expert Comment

by:Ray Paseur
ID: 39656418
NP, I certainly understand.  I hope your daughter is coming along well.
LVL 110

Expert Comment

by:Ray Paseur
ID: 39680746
Thanks for the points and best of luck with the project, ~Ray

Author Comment

ID: 39680766
Thanks, Ray!

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (  It addresses one of the most common problems that plague beginning PHP develop…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question