7 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 ...
Bakly's user avatar
  • 171
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, ...
David Browne - Microsoft's user avatar
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 ...
Joel Brown's user avatar
  • 12.5k
6 votes
Accepted

Many to many between lots of tables?

Create a supertype entity CarBody for subtypes Sedan, SUV, ... and move all general information to that supertype. Your M-M relationship will be between CarBody and Feature. See this topic for ...
Serg's user avatar
  • 1,266
5 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/
Cao Phong's user avatar
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 ...
Bill Karwin's user avatar
  • 14.7k
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 ...
jcolebrand's user avatar
  • 6,354
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 ...
Michael Green's user avatar
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 ...
Tom V's user avatar
  • 15.7k
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 ...
jynus's user avatar
  • 14.9k
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....
joanolo's user avatar
  • 13.4k
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 ...
Laurenz Albe's user avatar
  • 51.6k
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 ='...
Biju jose's user avatar
  • 2,088
2 votes
Accepted

Designing table for document annotation with future columns

JSON documents are ideal for this purpose. MySQL can store JSON documents via its JSON datatype, and it has a variety of function for extracting information in such documents. However, MySQL doesn't ...
Ezequiel Tolnay's user avatar
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 ...
Rick James's user avatar
  • 78.1k
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 ...
Joishi Bodio's user avatar
  • 3,378
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 ...
Rick James's user avatar
  • 78.1k
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 ...
Evan Carroll's user avatar
  • 63.1k
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 ...
Evan Carroll's user avatar
  • 63.1k
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 ...
Peter's user avatar
  • 2,257
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';
sticky bit's user avatar
  • 4,834
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 ...
Akina's user avatar
  • 19.9k
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 ...
Rahul Khimasia's user avatar
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, ...
RDFozz's user avatar
  • 11.6k
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 ...
McNets's user avatar
  • 23.7k
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 ...
Kondybas's user avatar
  • 4,338
2 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 ...
Kirk Saunders's user avatar
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 ...
Laurenz Albe's user avatar
  • 51.6k
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 ...
Bill Karwin's user avatar
  • 14.7k
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 ...
Hannah Vernon's user avatar
  • 70k

Only top scored, non community-wiki answers of a minimum length are eligible