Go Premium for a chance to win a PS4. Enter to Win


New database project | Database structure help

Posted on 2013-11-08
Medium Priority
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

Accepted Solution

FriedTyGuy earned 668 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 31

Assisted Solution

hnasr earned 668 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 664 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 111

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 111

Expert Comment

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

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

885 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