2

I'm having a problem, that I could solve programmatically, but for my personal (test) project I'm trying to see if I can fix it in the relational model.

Imagine a complex fashion product Pants with these Properties as dimensions:

  • Length (35,36,37,38)
  • Size (32,33,34,35)
  • Color (White, Red, Blue, Yellow, Green)
  • Kneepatch (Woodcutter, Brown, Silver)
  • Linked-belt (Brown, Beige, Black)

So there are 4*4*5*3*3 = 720 product variants. I know this is extreme, but I'm purely doing this to immediately test if my system will be able to handle it in a later phase.

So trimmed down I have:

Products

id
name

Properties

id
name

Thinking of RDBMS logic aside I would get a table like this, which of course isn't possible as it would constantly add properties and adds tons of nulls:

Productvariants

 id
 product_id
 property_1_id
 property_2_id
 property_3_id
...etc.

So this is a bit harder than a usual Color/Size combination you normally have on a product.

Now I can fake this methodology and just dump a serialization of Property ID's in one field on the Productvariants, like so:

Productvariants

id
product_id
properties {1, 2, 3}

.. and then split them in my ORM, but I'm afraid this will greatly increase the needed queries in the future (with tens of thousands of product variants) and optimization doesn't seem possible anymore that way.

Does anyone have experience with this and how to maybe intelligently do this? I'm using MySQL.

  • Based on my own experience with 'fashion objects' I find the properties aren't really dynamic, and that you're better off creating a proper structure; table for color, size, length (yes a table for size and length as they differ across markets e.g. EU sizes differ from USA sizes) etc and tie the relevant items together in a 'variant table'. That way you can easily enforce integrity and let the DB engine do the work it does best. – Allan S. Hansen Feb 21 '14 at 6:45
  • Why is 720 possibilities a problem? A modern RDBMS can handle tens of millions of records on commodity hardware. – Jon of All Trades Jan 6 '15 at 21:32
1

Products

ProdId
ProdName

Properties

(PropId) (primary key, if required)
ProdId (composite key)
PropCode (composite key)
PropValue (composite key) (if storing a specific value, otherwise you can remove this to simply store the possible properties for any given product)

Basically you store multiple values for a given property that all point back to a single product. The composite key would need to be unique and you would need to ensure data integrity by either linking PropCode to another table or restricting the values on entry.

| improve this answer | |
1

I have had experience with something similar (I believe), a scientific database that could have thousands of different field types and new ones coming along all the time. Here is how we solved it, similar to what it appears you are attempting. The problem is (as someone suggested) reconstructing the rows into a sensible format and displaying in an application. We did this in 2 sweeps - first by dynamically creating the screen from data stored in the database (field locations/types etc) then populating the data from the values available.

First we created a Value table which would store the values of each field. This table would hold all the values, for you it would look something like:

ValueID
ItemID
FieldID
Value (e.g. "32" for size 32, "White" for a colour field etc)

Then we created an Field table which would hold the description of each field, for you it would look like:

FieldID
FieldName (e.g. Colour,size)
ScreenID
FieldDisplayName (used for displaying the name on the screen)
FieldDisplayNameXLocation (these next 4 define the location on the screen of the label and editing field)
FieldDisplayNameYLocation
FieldXLocation 
FieldYLocation
FieldSize
FieldType (this is used for checking values are valid and for what type of field to display)

And finally a Screen table:

ScreenID
ScreenTitle
Screen Menus
etc...

We would then have a "Screen build engine" which, depending on the users screen selection, would pull the data from the screen and field tables, dynamically building the screen, something like:

SELECT ScreenTitle, ScreenMenus, ..., FieldID, FieldName, 
FieldDisplayName, FieldLocations, FieldSize, FieldType 
FROM Screen s INNER JOIN Field f ON s.ScreenID = f.ScreenID

That would then give us the data for the engine to build the screen. The screen would start as a blank canvas, with no fields on it, then the screen would be built using the field types/sizes and locations returned by the query. Secondly we would then extract the data from the Value table based on the selected Item to then insert into the visible fields:

SELECT ValueID, ItemID, FieldID, Value
FROM Field f INNER JOIN Value v ON f.FieldID = v.ValueID
WHERE ItemID = <user item ID entered>

This would return the values and the engine would then insert each value into the appropriate field on the screen.

This worked well once we had it implemented and it meant you could literally add new "fields" on the application and database side simply by adding rows to a table. Essentially each field is a row in the Field table, the rows defines what the field is. At one point we added 3 entirely new screens with fields and labels defined ready to insert data in about 3 seconds and the users didn't even have to close down their applications! There screens would refresh with the new database field and screen data as soon as they moved screens.

Obvious problems with this approach were:

  • The need to create an engine to build screens plus the associated overhead of maintaining such an application that does not work in a normal way (we were VB6/SQL Server 2000 backend over a 10 Mb LAN, worked fine).
  • A double hit on the database to build a screen, then to populate it with values
  • The necessity to create business logic to test valid field values (i.e. make sure int's were int's and special classification fields were entered correctly, like 54.7D.98)
  • Interoperability with standard systems
  • Difficulties in displaying the data in a standard way requiring some complicated SQL
  • When many users were accessing the system (we often had upwards of 30 simultaneous users) the system would slow down considerably (obviously network speed/server speed dependency)
  • Complicated concurrency issues sometimes resulted in deadlocks and other potential data loss issues (solved mostly by only allowing a single person to edit a single item at a time)
  • Not good for distributed systems across WANs due to latency issues

Benefits are though:

  • The ability to add many fields and screens very quickly with little difficulties
  • Lower management overhead, we eventually built a reverse build engine a super user could use to define and draw the field they wanted, the X/Y location values and field types would be uploaded to the test database and pushed to live when ready
  • Very simple and understandable (once you get your head around it) database logic
| improve this answer | |
-1

If you want to fix it in the relational model you're going about it the wrong way. You need a table for every kind of fashion product. It means more SQL work but not, as might seem, infinitely more work. Instead of two tables, you might have 50. The payoff is in a simpler application logic and simpler, better-performing queries.

The DBMS will work for you only if you relate attributes by making them columns, not rows. The moment you put an attribute in a row, you prevent the server from enforcing domain constraints. And your queries will suffer as you try to reconstruct the table from the rows.

The set of attributes is more stable than you might think. Many application programmers seem to think the problem domain expands rapidly and unpredictably. In fact, we've had color, size, and length for a long time, and it will be longer still before we start selling shirts made of helium or shoes as singletons.

| improve this answer | |
  • 1
    This does not provide a dynamic solution. It is entirely plausible and indeed possible and likely that attributes will change. That some attributes will need to be stored against some product an not other etc. Having to change the schema and application to achieve this outcome would be a poor solution. – Zack Feb 21 '14 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.