8
votes
Schema design for products with multiple variants/attributes?
This is just the info from @lesandru reply, I really find it very useful, so credits to him and @sahalMoidu
Applying normalization to your problem the solution is as given. Run and see it on Fiddle
...
- 181
6
votes
Accepted
What normalization rules does this table break
This is a terrible pattern, but it doesn't actually break any normalization rules. The reason is that it's actually a change in what you are modeling. Instead of your database modeling, say, ...
- 47.3k
6
votes
Schema design for products with multiple variants/attributes?
Database Schema for Multiple Types of Products
Solution is here:
http://www.codingblocks.net/programming/database-schema-for-multiple-types-of-products/
- 71
6
votes
Accepted
Disadvantage of Changing database schema - Insert or delete new columns/tables in middle
The foundation of your system is your database. If you change your database schema then you also have to change your code. That means that keeping a stable data model helps to reduce the amount of ...
- 12.6k
5
votes
Accepted
Kinda EAV in SQL Server
All implementations of EAV, even if you have the kind of "metadata table" you are describing, result in the Inner Platform Effect:
The inner-platform effect is the tendency of software ...
- 15.5k
4
votes
Accepted
Can I do a conditional JOIN or conditional SELECT based on the data in a table?
What you really want is a table full of nullable fields, EAV is hard to get right, and with EAV you literally need three columns, not 6. It would be much easier to just keep adding nullable columns ...
- 6,356
4
votes
Alternatives to EAV model
EAV is a solution when the values to be stored cannot be identified during design-time but are captured at run-time. It sounds like your situation is slightly different - you know all the values to be ...
- 25k
3
votes
Accepted
need to change time value stored in VARCHAR column
Assuming the following table structure and table name:
MariaDB [freeradius]> SHOW CREATE TABLE users\G
*************************** 1. row ***************************
Table: users
Create ...
- 15k
3
votes
Are lots of columns or joins better in Django
I'm not sure how to phrase this, but this type of application isn't what Django is made for.
Django is made for simple CRUD based websites, with an admin interface that comes for free, but I don't ...
- 15.7k
3
votes
Join tables with same keys, second table has multiple values for key
You have two possibilities:
Two joins. Probably a good alternative if you do a limited search, and don't want all available attributes (i.e.: you ignore capital)
SELECT
hostname,
type_var....
- 13.5k
3
votes
Accepted
Need help in picking solution and understanding when to add columns vs EAV
Richard,
I really think this is a great question you have posed. My answer got longer than I anticipated so in summary I have 3 possible solutions based on what you asked. If I had to rank my ...
- 1,061
3
votes
Adding LIMIT changes PostgreSQL plan from good to a bad one
Tell PostgreSQL not to use the index by using
ORDER BY contacts__aggregated_1.value + 0 ASC
The problem is that PostgreSQL thinks it will be fastest if it scans contacts__aggregated in the ORDER BY ...
- 54.8k
3
votes
Accepted
INSERT or UPDATE the values from a table A into the columns of table B based on table A's value in its row?
select itemid,
MAX(case
when itemattributename ='Application' then itemattributevalue
ELSE NULL END),
MAX(case
when itemattributename ='...
- 2,088
3
votes
How to build an efficient EAV model over JSONB in Postgresql
You're basically in the standard world of hurt that EAV causes. There's no such thing as an efficient EAV model.
SQL databases have schemas in order to improve the efficiency of storage and processing ...
- 607
2
votes
Accepted
MySQL - Store date as a value with an ID?
It sounds like you have reinvented "Entity-Attribute-Value" schema. This will be painful in the long run. Meanwhile...
Do not "normalize" dates or other 'continuous' values (such as FLOAT).
The ...
- 78.9k
2
votes
Accepted
MySQL - Is it good to have millions of tables in a single database?
Absolutely not.
Each table takes at least one file in the directory that represents the database. The OS overhead is non-trivial. The OS effort to find a single file (table) in a million-file ...
- 78.9k
2
votes
Accepted
ALTER TABLE online by keeping data as BLOBs
That's not what this is saying. Few systems can change a schema without taking the system offline -- even modern databases typically require exclusive table locks and table-rewriting to remove a ...
- 64.2k
2
votes
When 3rd NF is not the best solution? (Performance)
I have found that a good solution to problems like this is to pretend like you're interfacing with a 3rd party data source. Create a 4th table that is specifically designed to hold the data that is ...
- 3,398
2
votes
Accepted
EAV with multiple values per attribute
Your answer
more than one attribute with same name (Color for example),
So then create a different attribute with more specificity.
I am also required to store the image photo url.
This is ...
- 64.2k
2
votes
What normalization rules does this table break
I agree with David Browne's answer and Lamak's comment. They helped me a lot to do my analysis.
As Lamak mentioned in his comment, such a model is known as an EAV model. Although the EAV model may ...
- 163
2
votes
need to change time value stored in VARCHAR column
If I haven't missed something, a simple replace() should do it.
UPDATE elbat
SET value = replace(value, '12:00:00', '00:00:00')
WHERE attribute = 'Expiration';
- 4,924
2
votes
Accepted
Query to find products with all the given attributes
You can use the INTERSECT for this.
SELECT product_id from product_attributes
WHERE att_val1 = 'SS120' AND att_val2 = '1080'
INTERSECT
SELECT product_id from product_attributes
WHERE ...
- 2,412
2
votes
Accepted
How to query a table with different values on the same fields?
It is the common EAV.
I have looked through other solutions, some suggesting to use the IN clause (i.e. fieldid in (2, 8, 4, 1)) combined with GROUP BY (on the courseid) and HAVING, but that also ...
- 20.2k
2
votes
Accepted
PRODUCT_ID as primary key rather then the UPC(UNIVERSAL PRODUCT CODE)
Auto-increment IDs are what's called surrogate keys. In most cases, data has some sort of a natural key as well. In your case, it sounds like the natural key is the UPC.
So, if there's a natural key, ...
- 11.7k
2
votes
Accepted
pulling multiple entries from a match of two tables
You should filter using pairs:
WHERE (attribute_entries.title = 'ford' AND attributes.title = 'manufacturer')
OR
(attribute_entries.title = 'whatev' AND attributes.title = 'nickname')
Or ...
- 23.8k
2
votes
How To Store EAV Data When It Has Nested Objects/Entities
Proposed data structure is a tree. You have the root node having leaves and trunks attached to it. Each trunk also have a leaves/trunks. Here leaves are the attributes/values while trunks are the ...
- 4,566
2
votes
Does PostgreSQL support wildcard indexes on JSON?
This won't work. In particular, you cannot create a single index that will support conditions like
WHERE (user_properties ->> 'some random property')::date >= '2000-01-01'::date
for ...
- 54.8k
2
votes
Accepted
How to store multiple data types for same column (for many columns)
I'm the author of the book you referenced. Thanks for reading my book!
I suggest you read chapter 14, "Fear of the Unknown."
For your case:
I am entering the quantity of elements detected ...
- 15.5k
2
votes
INSERT or UPDATE the values from a table A into the columns of table B based on table A's value in its row?
To complement the answer already provided by Biju jose, here is one way to do it with dynamic SQL. If you add new rows to the dbo.eav table in my example, they automatically translate into new ...
- 70.3k
2
votes
EAV or JSONB column for dynamic data that needs good indexing
But everyone seems to strongly advise against EAV every time this comes up
A key question here is whether this is (1) a real business application, or whether it is (2) just a hobby project (or ...
- 607
Only top scored, non community-wiki answers of a minimum length are eligible