Table Design Suggestion
-
I have to maintain some metadata lists in DB, I have 2 options to design underlying simple table, 1st:
NAME VALUE
dept HR
dept fin
role engineer
role designerUNIQUE CONSTRAINT (NAME, VALUE) and some other columns like auto generated ID, etc. 2nd:
NAME VALUE_JSON_CLOB
dept {["HR", "fin"]}
role {["engineer", "designer"}]UNIQUE CONSTRAINT (NAME) and some other columns like auto generated ID, etc. There is no DELETE operation, only SELECT and INSERT/UPDATE. In first advantage is only INSERT is required but SELECT (fetch all values for a given NAME) will be slow. In second SELECT will be fast but UPDATE will be slow. By considering there could be 1000s of such lists with 1000s for possible values in the system with frequent SELECTs and less INSERTs, which TABLE design will be good in terms of performance. Thanks in advance.
-
I have to maintain some metadata lists in DB, I have 2 options to design underlying simple table, 1st:
NAME VALUE
dept HR
dept fin
role engineer
role designerUNIQUE CONSTRAINT (NAME, VALUE) and some other columns like auto generated ID, etc. 2nd:
NAME VALUE_JSON_CLOB
dept {["HR", "fin"]}
role {["engineer", "designer"}]UNIQUE CONSTRAINT (NAME) and some other columns like auto generated ID, etc. There is no DELETE operation, only SELECT and INSERT/UPDATE. In first advantage is only INSERT is required but SELECT (fetch all values for a given NAME) will be slow. In second SELECT will be fast but UPDATE will be slow. By considering there could be 1000s of such lists with 1000s for possible values in the system with frequent SELECTs and less INSERTs, which TABLE design will be good in terms of performance. Thanks in advance.
Performance is a meaningless metric unless we know what your use case is. Also, the second is more like an object store, and would do better in a non-relational (NoSQL) system.
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
-
I have to maintain some metadata lists in DB, I have 2 options to design underlying simple table, 1st:
NAME VALUE
dept HR
dept fin
role engineer
role designerUNIQUE CONSTRAINT (NAME, VALUE) and some other columns like auto generated ID, etc. 2nd:
NAME VALUE_JSON_CLOB
dept {["HR", "fin"]}
role {["engineer", "designer"}]UNIQUE CONSTRAINT (NAME) and some other columns like auto generated ID, etc. There is no DELETE operation, only SELECT and INSERT/UPDATE. In first advantage is only INSERT is required but SELECT (fetch all values for a given NAME) will be slow. In second SELECT will be fast but UPDATE will be slow. By considering there could be 1000s of such lists with 1000s for possible values in the system with frequent SELECTs and less INSERTs, which TABLE design will be good in terms of performance. Thanks in advance.
I'd immediately rule out the second option. You should always try to avoid storing multiple values in a single column. Assuming the data is meant to be a set of look-ups for other records, I'd also try avoid the first option. Put each list in its own table - Departments, Roles, etc. That way, you'll be able to define foreign key relationships from the tables that reference them.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I have to maintain some metadata lists in DB, I have 2 options to design underlying simple table, 1st:
NAME VALUE
dept HR
dept fin
role engineer
role designerUNIQUE CONSTRAINT (NAME, VALUE) and some other columns like auto generated ID, etc. 2nd:
NAME VALUE_JSON_CLOB
dept {["HR", "fin"]}
role {["engineer", "designer"}]UNIQUE CONSTRAINT (NAME) and some other columns like auto generated ID, etc. There is no DELETE operation, only SELECT and INSERT/UPDATE. In first advantage is only INSERT is required but SELECT (fetch all values for a given NAME) will be slow. In second SELECT will be fast but UPDATE will be slow. By considering there could be 1000s of such lists with 1000s for possible values in the system with frequent SELECTs and less INSERTs, which TABLE design will be good in terms of performance. Thanks in advance.
Listen to Richard, I recently got told that us old folks did not know the modern way of doing things the "architect" want to put all the lookup data into 1 table. He ended up with 5 table and a dogs breakfast of different relationships maintained in the business layer and not the database where I wanted it. Use 1 table for each different type of lookup data, use a primary key (not a code) and foreign keys.
Never underestimate the power of human stupidity RAH
-
Performance is a meaningless metric unless we know what your use case is. Also, the second is more like an object store, and would do better in a non-relational (NoSQL) system.
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
As described above as well: There is no DELETE operation, only SELECT and INSERT/UPDATE. Also every time I want to select all possible values for given NAME. This is a kind of reference list to fill the autocomplete suggestions on UI. Hence fetch will be always all values and INSERT new only one value at a time.
-
I'd immediately rule out the second option. You should always try to avoid storing multiple values in a single column. Assuming the data is meant to be a set of look-ups for other records, I'd also try avoid the first option. Put each list in its own table - Departments, Roles, etc. That way, you'll be able to define foreign key relationships from the tables that reference them.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
It is a complex system where I don't know upfront what all possible reference list may exist in the runtime environment. Also, these value will never be referenced from any other table or column. A kind of auto-suggest list and user is always free to choose new value if not like any of the suggestions.
-
As described above as well: There is no DELETE operation, only SELECT and INSERT/UPDATE. Also every time I want to select all possible values for given NAME. This is a kind of reference list to fill the autocomplete suggestions on UI. Hence fetch will be always all values and INSERT new only one value at a time.
Manish K. Agarwal wrote:
Also every time I want to select all possible values for given NAME.
Not exactly what you said above, to be fair. It also doesn't say whether this is native or web-based, which has a relevant impact on performance of the system as a whole. If writes are rare in either case, you'll see better SELECT performance out of option 2 for one simple reason: assuming that you index the key, when it's found the appropriate key, it's done and will stop searching the table. If you're in a web environment, you have the added benefit of being able to send it straight down the pipe with no serialization/deserialization required. If all your model requires is selection speed, option 2 is the way to go. Option 2 is definitely not write friendly, though, nor can you use it to relate data down the road on the database level. If these are likely or even possible considerations, you might want to go with option 1.
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
