Developer forum

Forum » Feature requests » ItemTypes with a relational database schema instead of tables

ItemTypes with a relational database schema instead of tables

E-mail notifications
Kevin Steffer
Kevin Steffer
Reply
Posted on 12/10/2016 11:31:13

We are having difficulties merging Itemtype updates into existing solutions mainly because the database table of the itemtype has to updated and conflicts could occur.

Our suggestion would be to change the ItemType database model to reflect they way Product Categories and Product Category Fields are structured in the database. Then we would have a more flexible database model for modifying itemtypes.

Suggested database model

Table: ItemType

  • Id
  • Name
  • SystemName

Table: ItemField

  • Id
  • ItemTypeID
  • Name
  • SystemName
  • DataType
  • Kontrol

Table: ItemFieldStaticOptions

  • Id
  • LanguageId
  • ItemFieldId
  • Name
  • Value

Table: Item

  • Id
  • ItemTypeID
  • ItemFieldId
  • Value

etc.

Let me hear your thoughts on this one.


Replies

 
Nicolai Pedersen
Posted on 12/10/2016 14:42:38
Reply

Nice thought, but it is the pain in the ass for integrations and other systems, like Mage***, Umbra*** and a few others have some sort of issues with these models. It is nice with little data put in from the UI. But fill it with 100.000+ records and add searches, relatations etc you will get into troubles and need to code around it. So this was a consideration when we designed it - and we chose the db layout we created now which we believe holds the best performance, scalability and maintainability.

With this model, try to related 2 item types in a join kind of scenario and filter it by 2 fields on each itemtype and sort it by a third field. And have it perform in large quantities. Just try to write the SQL and you'll understand. Of course we could index the entire thing, but then we might as well skip the database and switch to document database models instead.

BR Nicolai