Solved

New database project | Database structure help

Posted on 2013-11-08
9
384 Views
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?
0
Comment
Question by:dimmergeek
9 Comments
 
LVL 1

Accepted Solution

by:
FriedTyGuy earned 167 total points
Comment Utility
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.
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 167 total points
Comment Utility
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.
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 166 total points
Comment Utility
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)
0
 
LVL 7

Author Comment

by:dimmergeek
Comment Utility
@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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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.
0
 
LVL 7

Author Comment

by:dimmergeek
Comment Utility
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.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
NP, I certainly understand.  I hope your daughter is coming along well.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Thanks for the points and best of luck with the project, ~Ray
0
 
LVL 7

Author Comment

by:dimmergeek
Comment Utility
Thanks, Ray!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now