Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unanswered: Design help required

    Hello to All!

    I want some help in my DB design. I have a Inventory DB, in which there is a Products table. Now each product can be available in multiple Types, Materials, Colors, and Themes etc. But I have to have only one record per product in the Products table no matter in how many colors or materials it is available in. Then I have multiple tables each for Materials, Colors etc.

    One way of connecting a product with different colors, Materials is having a table in b/w them that'd contain ID's from both Product and Colors, Materials Table like

    IDProduct IDColor and one table like

    IDProduct IDMaterial

    But this would end up in having alot of those in b/w tables, One each for Materials, Colors, Types, Themes and so on.

    One other way might be to save the info in the Products table it self in a comma separated field like:

    IDProduct IDType IDColor IDMaterial ...
    1 1,2,3 2,3 3,5,6

    Tell me which of these or some other is better way to save such information.


    I'd be very grateful.



    Regards,
    Sajid.

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Quote Originally Posted by sajid08
    One way of connecting a product with different colors, Materials is having a table in b/w them that'd contain ID's from both Product and Colors, Materials Table like...
    This is exactly what you should do. If you want to relate two tables together, you need to create a relational table. At least if you are going to have an m-to-n relationship, which you do.

    Quote Originally Posted by sajid08
    But this would end up in having alot of those in b/w tables, One each for Materials, Colors, Types, Themes and so on.
    Are you worried about space? The datatypes of the columns in those tables will not take up much room, and you'll really need to have a LOT of information in there to cause any sort of noticeable speed issue. Even if that does ever happen, if you have proper indexing, everything should be fine.

    Quote Originally Posted by sajid08
    One other way might be to save the info in the Products table it self in a comma separated field...
    If you did that, what would you do if you wanted to change some of the relationships after they had already been entered ? There would be some ugly parsing going on.
    Give a man a fish and you feed him for a day. Teach a man to fish and you lose a steady customer.
    -----------
    Eclectic Web Development (under heavy construction)

Posting Permissions

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