Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    May 2008
    Posts
    135
    Thanks
    13
    Thanked 10 Times in 10 Posts

    Split string and use value as foreign key

    hi all,

    am not sure if this is possible (or a good idea), basically i have a column that contains a string split by commas

    Code:
    1,42,44,12,64,12
    these numbers relate to ids of records in another table.

    if i delete the record with the id of 42 in the other table, i want to remove it from the string.

    with foreign keys you can set the on delete action to cascade (so if 42 would be removed when the record with id 42 is removed)

    basically i am wanting to achieve the same thing here, only using the mysql (not php, or any other external language).

    can i set a foreign key as part of a substring or something?


    the reason i have to do it in a string with commas, is there can potentially be unlimited values, so its not really a good idea to either have 100 columns i'll potentially never use, or create / delete columns on demand.

    or is it?

    any ideas would be great :-)

    thanks alot

    mike

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,498 Times in 4,462 Posts
    Nope. Really bad DB design. You should never store a delimited list of values in a single field.

    Time to learn about many-to-many tables.

    Example:
    Code:
    Table: foods
        foodid : int auto_increment primary key
        foodname : varchar(100)
    
    Table: people
        personid : int auto_increment primary key
        name : varchar(50)
        ... etc. ...
    
    Table: peopleFoods
        personid : int references people(personid) on delete cascade
        foodid : int references foods(foodid) on delete cascade
    
    ***********
    
    people:
        37 : james
        42 : mary
    
    foods :
        1 : watermelon
        2 : dried squid
        3 : chocolate covered ants
    
    peopleFoods:
        37 : 2
        37 : 12
        37 : 42
        37 : 91
        42 : 1
        etc.
    This is just *part* of the process called NORMALIZATION. Look it up and be prepared to spend a few hours (okay, at the least many minutes) reading about it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    May 2008
    Posts
    135
    Thanks
    13
    Thanked 10 Times in 10 Posts
    Hey old pedant, thanks for the reply.

    I understand the concept of normalization and many to many table design. The problem is there could be an unlimited number of values. Basically I have an attribute table, an attribute values table, and a attribute combination table. They are linked via foreign keys. But the attribute combination table can't be. Beacuse there could possibly be unlimited number of attributes and attribute values. So how am I supposed to link this up via foreign keys without creating a new column fir each attribute and subsequently deleting it when the attribute is removed?

    I.e

    ID | attribute_combination | price

    1 | 1,65,23,12 | 12.99

    so I could of have sperate columns for the attributes, but potentially there could be an unlimited amount of values in the combination.

    What do you think?

    Let me know if I'm not making sense it's quite hard to explain.

    Thanks

    Mike

  • #4
    Regular Coder
    Join Date
    May 2008
    Posts
    135
    Thanks
    13
    Thanked 10 Times in 10 Posts
    here is my schema:

    Attribute table:
    Code:
    ID | ATTRIBUTE 
    ---------------
    1  |   Colour
    2  |   Size
    attribute values table:
    Code:
    ID | ATTRIBUTE_ID | ATTRIBUTE_VALUE 
    ---------------------------------------
    1  |         1    |     Red
    2  |         1    |    Green
    3  |         1    |    Blue
    4  |         2    |    Small
    5  |         2    |    Medium
    6  |         2    |    Large
    
    attribute_id foreign key for id in attributes table
    attribute combination table:
    Code:
    ID | ATTRIBUTE_COMBINATION |   PRICE
    ---------------------------------------
    1  |    1,2,3              |    2.99
    2  |    4,5,6              |    10.99
    3  |    6,1,4              |    14.99
    4  |    1,2,3,4,5,6        |    99.99
    
    each number delimeted in the attribute_combination refers to the id of a record in the attribute values table. so if they where split up, they would be foreign keys. but there not split up.
    so you see my problem? the combination could be 3,6,50,100 values etc.

    so would i have to create a new column for each item in the delimeted list? 100 items would mean 100 columns?

    surely theres a better way than this? this is why i thought a delimted list was the best way.

    any help would be great.

    thanks

    mike

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,498 Times in 4,462 Posts
    One more time: Do *NOT* put delimited lists into a database field.

    Period.

    Correct contents of your attributeCombination table could be (example):
    Code:
    ID | ATTRIBUTE_VALUE_ID    |   PRICE
    ---------------------------------------
    1  |    1                  |    2.99
    1  |    2                  |    2.99
    1  |    3                  |    2.99
    2  |    4                  |    10.99
    2  |    5                  |    10.99
    2  |    6                  |    10.99
    3  |    6                  |    14.99
    3  |    1                  |    14.99
    3  |    4                  |    14.99
    4  |    1                  |    99.99
    4  |    2                  |    99.99
    4  |    3                  |    99.99
    4  |    4                  |    99.99
    4  |    5                  |    99.99
    4  |    6                  |    99.99
    If you insist on using delimited lists in DB fields, then you will be stuck with many more problems than you have encountered so far.

    (I suspect that Price does not belong in this table, but that's another question.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    May 2008
    Posts
    135
    Thanks
    13
    Thanked 10 Times in 10 Posts
    hey thanks again old pedant

    the problem is the combination of multiple attribute values affects the price of product.

    so if a product is red and small price = £12.99

    if a product is gold and xxl price = £15.99

    if a products is gold and xxl and has a zip price = £18.99

    do you see what im getting at?

    red and small = 1,2
    gold and xxl = 3,4
    gold and xxl and zip = 3,4,5

    hence why im delimitering them.

    but my problem is the combination could be 2 values or 10 values, totally dynamic so i cant have fixed columns right?

    do you see what im trying to solve?

    i realize delimtered values in fields is bad design, im looking for an alternative to solve my problem above.

    thanks

    mike

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,498 Times in 4,462 Posts
    You know, you *could* do it this way:

    Code:
    AttributeNames table
    aNameID : aName
           1  |   Colour
           2  |   Size
    
    AttributeValues table
    aValueID : aValue
          1  : Red
          4  : Large
    etc.
    
    ProductAttributes table
    prodID : aNameID : aValueID
     7783  :      1  :     1
     7783  :      1  :     2
     7783  :      1  :     3
     7783  :      2  :     4
     7783  :      2  :     5
     7791  :      1  :     3
     7791  :      2  :     4
     7791  :     16  :     136
    So each product (or whatever it is you have attributes for) could have as many NAMED attributes, each with as many VALUES, as you needed.

    That table, for example, says that product 7783 comes in 3 colours and 2 sizes.

    Now, if it only comes in one color in size large (but all 3 colors in size small), and *that* is what you are trying to express, then yes, you probably need to replace "prodid" in that table with "attributeCombinationID" and then have yet another table that associates prodid with attributeCombinationID. In a sense, attributeCombinationIDs become sub-products.

    Where something like this gets complicated is when, for example, some attributes are available for all sub-products and some aren't. You don't really want to have to duplicate all the information at the sub-product level for the attributes available to all sub-products, even that's the theoretically correct way to do it. I'm not a fanatic about being theoretically correct if a bit of *careful* denormalization can help keep things simpler or faster, so I would feel too bad about maybe doing:
    Code:
    ProductAttributes table
    prodID : subProdID : aNameID : aValueID
     7783  :     null  :      1  :     1
     7783  :        1  :      1  :     2
     7783  :        2  :      1  :     3
     7783  :     null  :      2  :     4
     7783  :     null  :      2  :     5
    Which says that all subproducts of 7783 come in red, but only subproduct 1 comes in green and only subproduct 2 comes in blue. You can probably figure out other variations on this scheme.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,498 Times in 4,462 Posts
    LOL. We posted at same time.

    FWIW, your problem isn't unique. All big stores of course have the same problem.

    I was helping another person on another forum with something similar. And his solution was indeed the concept of subcategories that only applied to some categories.
    Not quite as complex as your problem, but the idea of using NULL to mean "all" worked for him.

    But I suspect that the really big stores probably do go with full normalization, just because in the long run it's easier to manage with conventional DB tools. And, yes, every single SKU has its own list of attributes. For all the tens of thousands of SKUs.

    Gotta go for now. Will check back in later or maybe tomorrow.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Regular Coder
    Join Date
    May 2008
    Posts
    135
    Thanks
    13
    Thanked 10 Times in 10 Posts
    hmmmmm, i guess really each variant, should be classed as its own product.

    each one is going to have a different price, a different SKU, a different stock level.

    although on the flipside they will also share the same product name,product desc, product images, product reviews etc.

    so if i was to put them in the same table, i would be repeating portions of data over and over again, which goes againest normalization.

    and if a product comes in 7 sizes in 10 different colors, thats 70 entries for 1 product. i sense this will get very messy, and awfully complicated.

    what are your thoughts on this?


    i will have a look at how other ecommerce solutions try and handle this.

    thanks

    mike

  • #10
    Regular Coder
    Join Date
    May 2008
    Posts
    135
    Thanks
    13
    Thanked 10 Times in 10 Posts
    i think the only way of achieveing this with keeping within the rules of normalization is to:

    1. have a products table. stores the core product information.
    2. have an attributes table. stores available attributes to the products.
    3. have an attributes values table. stores the values available for each attribute.
    4. have a product variants table, which stores all combinations of attribute values for each product. in this table will be the variant price, variant SKU, variant stock level etc.

    to then make this adhere to normalization, the product variants table needs to have a field available for each attribute.

    this would mean when adding an attribute, altering the product variants table structure (adding a field) and subsequently removing the field when the attribute is removed. this would also mean removing any variants that depend on this attribute being present (obviously remove these before removing the field).

    this seems the only logical way, other than storing delimeted strings. a delimeted string would achieve the same results, although cascading updates etc. would not be achieveable.


    which solution would you go for?

    man, this is really playing on my mind.

  • #11
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    As you have just written, I think that is what Old Pedant suggested.

    The table you mention firstly, in your last post - products table - could storee the core product details and the product code. That propduct code could then be your prod_id in the third table OP mentioned.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #12
    Regular Coder
    Join Date
    May 2008
    Posts
    135
    Thanks
    13
    Thanked 10 Times in 10 Posts
    hi bazz

    yeah i was just thinking out loud.

    my problem is do i delimit the string, or have seperate columns that are dynamically created / destroyed. or if this a better method. seems a bit overkill. but i aint to sure


  •  

    Posting Permissions

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