#1
  1. Webmaster Extraordinaire
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Location
    Woburn, MA
    Posts
    126
    Rep Power
    15

    Unhappy Help with DB Design


    I'm trying to figure out an efficient way to design a database for a site I'm working on. It is for a car parts re-seller. All of the parts are fit into categories and one part may fit into just one or several categories. The same thing goes for the fitlist (meaning the cars that the part fits). The part may fit just one car, but it may also fit 50.

    Right now, I have the fields with fitlist and category separated by commas, but it really isn't working like I need it to. I run into a problem when I need to display all of the categories for which there are parts for a specific car model. The database only sees the first category in the list for each part, so that if there is three categories listed for one part, the second two are ignored.

    I have no formal training in database design, and I suspect there are other problems with how I do it, besides the one outlined above. My database design for the parts looks like this:

    - item_id (part identifier)
    - item_description (short description for item)
    - description_for_sales (full description)
    - sales_price_1
    - categories (this is a comma separated field with the category IDs from another DB of categories)
    - fitlist (this is a comma separated field with the model IDs from another DB of car models)

    I'm wondering if there is a better solution to this problem, that I haven't thought of. My basic problem is that there could be only one item in the fitlist or categories field, but there could be 50.
    Craig Anderson
  2. #2
  3. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    27
    You would need to use a few tables in order to get a 'decent' normalized layout. You *can* use the layout you described, but as the tables start to grow, it will be anything but efficient...

    So here's something to chew on:
    Code:
         cartypes               items                                      categories
         --------               -----                                      ----------
      ---car_id            |----item_id -------------                  |---cat_id
      |  description       |    short_description   |                  |   description
      |                    |    long_description    |                  |
      |                    |    price               |                  |
      |                    |                        |                  |
      |                    |                        |                  |
      |                    |                        |                  |
      |        item_car    |                        |   item_category  |
      |        ----------  |                        |   -------------  |
      |        item_id------                        |---item_id        |
      |--------car_id                                   cat_id----------
    Cartypes, Items and Categories all contain an auto incremented id (the primary key), which is used to uniquely identify the row. This key is then inserted into the 'lookup table' (item_car/item_category), which is used to join the tables on those fields. So if the item_id 1 fits in 5 cartypes, you would insert 5 rows in the item_car table where item_id would be 1 for all instances, but the car_id would change accordingly. And the same goes for items<->categories in the item_category table.

    The tables item_car and item_category could be merged into one table insted of two, but it depends on how you intend to use them.

    Well, it's just a suggestion...

    //NoXcuz
    UN*X is sexy!
    who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
  4. #3
  5. Webmaster Extraordinaire
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Location
    Woburn, MA
    Posts
    126
    Rep Power
    15
    Sounds like you're a real viking, coming from Sweden - at least more so than myself. I can only claim to have great-grandparents from Sweden.

    Would the 'lookup tables' be automatically generated anytime a part is added into the 'items' dB? Would it be permanent? Then when I was looking for a part that fit a certain car, I would be able to get a decent lookup. I see the rationale, but it sounds like this new dB (I don't see any reason not to combine the two 'lookup tables') would be very large? Indeed we have probably 15,000 parts. Would this get so large that it would be very, very slow?

    Also, here's an example. Say I have a part with an ID of 1 that has three categories (a,b,c) and fits on 2 cars(i, ii). Would I need to have 6 entries like the ones below (part_id, car_id, category_id)

    1,a,i
    1,a,ii
    1,b,i
    1,b,ii
    1,c,i
    1,c,ii

    Just trying to make sure I understand well enough.
    Craig Anderson
  6. #4
  7. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    27
    Well, Germany is not that far away from Sweden, and I was pleased to see another viking in here...

    You would need to do two (or three if you have two lookup tables) insert queries when you insert an item into the database. First insert the item itself into the items table, then fetch the id which this item got and insert it into the lookup table(s). If you're using a db that supports triggers/functions, this would be perfect situation to use one to automagically insert the information into the lookup table.

    And the lookup table(s) would have to be permanent, as they hold the relationship between the item, the category and the car type. The important part here is to make sure that the referential integrity (the relationship between the tables) is not compromised, since that would lead to disaster...
    If the database you're using supports referential integrity through foreign keys, the better for you as that should be handled at the database level and not with application code.

    The lookup table would have lots of rows, but you would only be storing integers in them, so the amount of data in that table is not that large. And with proper indexes on those columns it won't be a problem, so I don't think you have to worry about speed issues here. Assuming that you perform more selects than inserts/updates/deletes on the table of course.

    The example you provide is exactly the way I meant it, so I'd say that you've grasped the concept.

    //NoXcuz
    UN*X is sexy!
    who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
  8. #5
  9. Webmaster Extraordinaire
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Location
    Woburn, MA
    Posts
    126
    Rep Power
    15
    It's early here, so forgive me if this doesn't make all the sense in the world. Oh, and I forgot. It's actually Berlin, Massachusetts one of the many states in the Northeast United States that pretends to have a Berlin.

    I'm using MySQL, but I'm not sure if it supports these items you mentioned. Triggers/functions sound to me like when one record was put into dB A, another record would automatically be placed into dB B. This true? If so, does MySQL support it, and how?

    Also, I guess I'm not quite sure what you mean by database level referential integrity. I understand that the dBs need to be linked by the foreign keys, or all will fail. For instance, the lookup table will be comprised of 3 foreign keys, one each to the category table, the car models table, and the items table. Is there a way to make this happen other than adding an addtional INSERT to the program that I've written so internal employees of the company can easily control the parts?
    Craig Anderson
  10. #6
  11. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    27
    Sorry, I wrote that at 2 AM yesterday and I saw Berlin MA, but it didn't quite reach my brain at the time...

    MySQL doesn't support triggers, but by using InnoDB tables MySQL supports foreign keys. So the referential integrity would have to be handled by some code instead of by the database itself. It's possible to do it that way, but there are lots of advantages if you can let the db handle it.
    A trigger is exactly what you think it is, pre-defined actions that are triggered when a specific event occurs. Like when you insert something into one table, the trigger automagically inserts something else into another table.

    The referential integrity is the relationship between the entries in your tables. Take your example with item 1 in your previous post.
    Item 1 belongs to category a,b and c, and fits into car i and ii. Those six entries in the lookup table tells you that it's so, and their relationship is defined by the six entries. What if you suddenly update the categories table, and let b stand for doors instead of tires, but you forget to change the entries in the lookup table. Then the relationship between the tables would be messed up, and your search results etc would not be correct anymore. Thus the referential integrity would be broken. So the point with foreign keys is that you can't break this referential integrity; like if you change category b, the lookup table would also be changed accordingly. This is probably not the scientificly correct explanation, but something like that...

    All in all, you would have to make up for the lack of triggers by creating an additional insert query when you insert a new item. And the relationship could be handled by InnoDB tables or when using regular tables, by using code to make sure that you can't mess up the relationship between the tables' entries.

    //NoXcuz
    UN*X is sexy!
    who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep

IMN logo majestic logo threadwatch logo seochat tools logo