Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2014
    Posts
    3

    Angry Unanswered: Automotive Parts DB-Primary Key Advice

    I'm new here and a little rusty on my database design. I am building a database in MySQL for my friends transmission parts business for use with a cascading drop down menu on his web site. The type where you pick your car's YEAR, then MAKE, MODEL, ENGINE, and then it shows them the TRANSMISSION model number they need for their vehicle(That is the only data I have to work with from the supplier catalogs). That's how they are listed in his suppliers catalogs he has provided me. The infuriating thing is: none of them list bin or sku#'s I could possibly use as a primary key. Thought I might be able to use the transmission model number listed, but it turns out some of them are used on more than one model, and even multiple makes. One can be used by Cadillac, Acura, and Toyota. Its a real headache. What would you guys do? Should I just create my own generic sku/bin for a primary key? Combine two data elements? I haven't built a database in 5 years, so this is a bit of a refresher for me. Any and all advice would be most welcome. Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use the transmission model number...

    Your friend isn't going to stock transmissions or parts by vehicle, they'll stock them by transmission model.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2014
    Posts
    3

    Well

    Thanks but that doesn't help here. This isn't for his use but potential customers use on his web site so they can easily find the transmission model number for their vehicle. Like I said this is for a cascading relational drop-down menu for the customer to use. They will select YEAR, then MAKE, MODEL, ENGINE, and it will query the database for their transmission part number. I can't use the Transmission part number because some are used in multiple models and multiple makes. MySQL spits out an error if I try using it for more than one vehicle as the primary key.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The transmission model number ought to be the primary key in the transmissions table. It should be a foreign key in the vehicles table that has the year, make, model, engine, and transmission as foreign keys to other tables. MySQL will happily allow this usage.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2014
    Posts
    3
    I see what you're saying, I should have two tables instead of one table. I had someone else(friend who codes SQL for a living) advise me to put everything in one table and create an "in-house" sku/bin. Thanks for putting up with what must seem to be a sophomoric question. I am okay at building them, design always trips me up though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •