Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
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 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)

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What we learned in Webroot's webinar on multi-vector protection.
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 is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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