1

So I'm trying to figure out the best way to go about handling this database design wise. This is a rough idea of how I THINK it should go, but not really sure.

If I have orders that have their own necessary attributes that a user can create for all of their orders to have, and if the order can be comprised of items, and those items also can have their own custom attributes, and not necessarily the same attributes as the order, what makes the most sense?


Orders  
====== 
id  
client_id 

Items  
=====
id  
user_id  
name 

Attributes  
========== 
id  
user_id  
is_order_attr  
is_item_attr  
name  
type 

Orders_Attributes
=================
id
order_id
attribute_id
value

Items_Attributes
================
id
item_id
attribute_id
value

A) Have an Attributes table for Orders and an Attributes table for Items, subsequently storing the values in another table, say Orders_Attributes_Values and Items_Attributes_Values?

B) Storing everything in a JSON Data Type column in some type of Orders_Configs table and then storing the values in additional tables with the JSON Data Type?

or

C) Something completely different?

  • 1
    I do not recommend to have an intermediate Attributes table joined to two separate tables. It seems to be redundand. Or use it as a template storage while creating a record in ????_Attribute table and do not attach to any table in DB schema. – Akina Aug 24 '18 at 5:54
  • 1
    A main consideration should probably be how you want to use the data. If you just want to return it in a blob so the application can use it to display, then JSON isn't a bad way to go. If however you want to be able to use SQL to query on it / aggregate etc. then it may be better in a relational schema. I know SQL 2016 and beyond can query JSON data but like XML querying it's neither fun or efficient. – Matthew McGiffen Aug 24 '18 at 11:04
0

A small JSON blob on Items to cover the wide range of descriptors of a item might be prudent. The rest of Orders / Clients tables should be fairly structured and not need any attributes.

Considering attribute tables as a primary design will get you into a mess when queries 3. EAV Tables — Entity-Attribute-Value Tables. A sharded Attribute table between a variety of other tables will make this even worse.

  • How are search queries with using a JSON column? Is it something that could/will eventually be a major issue? I also was considering making a different table for the more custom item types that I might deal with, maybe also have the items table with 4-5 columns for custom attributes? – smb Sep 7 '18 at 20:18

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.