0

There are alot of questions for my "problem" or "challenge". But none of them really fit my needs. Okay, here I go:

I want a database which holds variable attributes. Now I "hardcoded" them into the database (see image) as a columnname. See "color" and "size". But what if I want a new attribute? For example "model". I need to adjust the database and add a column. I don't want this.

enter image description here

Do you have any ideas? I'm thinking for a solution the past few months, and nothing good so far...

Rick James
  • 135,179
  • 13
  • 127
  • 222
Refilon
  • 3,334
  • 1
  • 27
  • 51
  • 1
    What about a colum in the attribute table called 'attributeType' which could hold 'color' or 'size'? Another column could then be 'attributeValue'. – KIKO Software Mar 17 '17 at 21:28
  • @KIKOSoftware what if I have products that have both color and size. How would I do that? – Refilon Mar 17 '17 at 21:28
  • One row in the products table could have multiple rows in the attributes table. – KIKO Software Mar 17 '17 at 21:29
  • @KIKOSoftware yes, I am aware of that, but how do you keep stock? For example, color red and size S we have 4 in stock, but red and size L we have 0 – Refilon Mar 17 '17 at 21:30
  • Stock could be an attribute like size or color, or you can put that column in the products table. – KIKO Software Mar 17 '17 at 21:30
  • How do you keep stock of red size S 4 and red size L 8? Because when you do it in the products table, it will count for everything, and when you do it as an attribute, we dont know for what product specifically – Refilon Mar 17 '17 at 21:32
  • Yes, you can have cases where product and attributes should be combined to make a stocked item. Clearly you would need a more advanced design for that... but that's beyond your question. – KIKO Software Mar 17 '17 at 21:34
  • 1
    Sounds like you need the key value pair – MIKE Mar 17 '17 at 21:34
  • @KIKOSoftware that's exactly what I am asking for. An idea for a more advanced design :) – Refilon Mar 17 '17 at 21:35
  • @MIKE, what do you mean by that? – Refilon Mar 17 '17 at 21:35
  • Or [EAV](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) – Paul Spiegel Mar 17 '17 at 21:36
  • Any more detailed answers please? Do not fully understand how I can make a better database design with EAV or key-value-pair? – Refilon Mar 17 '17 at 21:41
  • Ignore that key-value thing. :-) Databases like this will become more and more complex. I cannot judge how far you want to take this, so it is difficult to create a more advanced design. I can give you a tip: Start by creating a separate table for 'stocked items', and for 'products', then let the 'stocked items' have attributes and each products can have multiple items (variations of the same product with different attributes). – KIKO Software Mar 17 '17 at 21:44
  • @KIKOSoftware I know right, been bashing my head on this question for months now... Can not get to a good solution. When each attribute is a different row, how can I keep stock then? That's actually the main question here.... :-) – Refilon Mar 17 '17 at 21:50
  • Possible duplicate of [database schema for products attributes](http://stackoverflow.com/questions/14194841/database-schema-for-products-attributes) – Joel Brown Mar 18 '17 at 14:36
  • See also http://stackoverflow.com/questions/11779252/entity-attribute-value-table-design/11972029#11972029 – Joel Brown Mar 18 '17 at 14:37
  • This forum is loaded with people asking similar questions; study them. – Rick James Mar 18 '17 at 20:31
  • This looks like a duplicate of this: http://stackoverflow.com/questions/41911659/designing-a-database-that-handles-inventory-tracking-with-product-variations/41915622 – Panickos Neophytou Mar 20 '17 at 00:14

1 Answers1

0

So, after all the comments, this is what it now looks like. First the products table:

table: products
column: id
column: name
column: categoryId

Then the table with the stocked items. Each variation of a product should be respresented here:

table: items
column: id
column: productId
column: stock
column: price

Finally the table that contains the attributes of the stocked items:

tablet: attributes
column: id
column: itemId
column: type <color,size>
column: value <yellow,brown,large,small>
KIKO Software
  • 15,283
  • 3
  • 18
  • 33
  • So are you saying, that Size S and color red, is productID 1 and size M and color red is productID 2? But then I get a whole bunch of records in my database. Would that not hurt performance? – Refilon Mar 17 '17 at 21:53
  • No. A small red product is the same product as a medium red one, but it is a different stocked item. And you can also give it a different price. basically the `items` table creates a collection of attributes, and a stock number and price, for a certain product. – KIKO Software Mar 17 '17 at 21:54
  • Okay, but how would you know the difference between a red S and a red M in stock list then? Because they have the same productId, so they do share the stock than also, right? – Refilon Mar 17 '17 at 21:55
  • They will be represented in two different rows of the items table. They do not 'share' stock, at least that is what I assumed. – KIKO Software Mar 17 '17 at 21:57
  • I don't think that's needed. Just go over it by yourself. Just remember: One stockable item can have multiple attributes and one product can have multiple stockable items. It's not really that difficult when you realize this. – KIKO Software Mar 17 '17 at 22:00
  • I know what you mean, problem is, how do I keep track of stock, and how to retrieve it from database... :( – Refilon Mar 17 '17 at 22:03
  • 1
    Just a warning, databases for webshops (I assume that is what this is for) tend to have dozens of tables and hundreds of columns. They get very complex very quick. That's why most shops use an existing solution. – KIKO Software Mar 17 '17 at 22:03