Proposed schema
First and foremost, here is an example of my proposed schema to reference throughout my post:
Clothes
----------
ClothesID (PK) INT NOT NULL
Name VARCHAR(50) NOT NULL
Color VARCHAR(50) NOT NULL
Price DECIMAL(5,2) NOT NULL
BrandID INT NOT NULL
...
Brand_1
--------
ClothesID (FK/PK) int NOT NULL
ViewingUrl VARCHAR(50) NOT NULL
SomeOtherBrand1SpecificAttr VARCHAR(50) NOT NULL
Brand_2
--------
ClothesID (FK/PK) int NOT NULL
PhotoUrl VARCHAR(50) NOT NULL
SomeOtherBrand2SpecificAttr VARCHAR(50) NOT NULL
Brand_X
--------
ClothesID (FK/PK) int NOT NULL
SomeOtherBrandXSpecificAttr VARCHAR(50) NOT NULL
Problem statement
I have a clothes table which has columns like name, color, price, brandid and so on to describe the attributes for a particular item of clothing.
Here's my problem: different brands of clothing require differing
information. What is the best practice for dealing with a problem like
this?
Note that for my purposes, it is necessary to find brand-specific information starting FROM a clothes entry. This is because I first display the information from a clothes entry to the user, after which I must use its brand-specific information to purchase the item. In summary, there has to be a directional relationship between clothes (from) and the brand_x tables.
Proposed/current solution
To cope with this, I have thought of the following design scheme:
The clothes table will have a brand column which may have id values ranging from 1 to x, where a particular id corresponds to a brand-specific table. For example, id value 1 will correspond to table brand_1 (which might have a url column), id 2 will correspond to brand_2 (which might have a supplier column), etc.
Thus to associate a particular clothes entry with its brand-specific information, I imagine the logic at the application-level will look something like this:
clothesId = <some value>
brand = query("SELECT brand FROM clothes WHERE id = clothesId")
if (brand == 1) {
// get brand_1 attributes for given clothesId
} else if (brand == 2) {
// get brand_2 attributes for given clothesId
} ... etc.
Other comments & thoughts
I'm attempting to normalize my entire database in BCNF, and although this is what I came up with, the resulting application code makes me feel very anxious. There is no way to enforce relations except at the application level, and thus the design feels very hacky and, I anticipate, very error-prone.
Research
I made sure to look through previous entries before making a post. Here's a post with a near-identical problem that I managed to find. I made this post anyway because it seems like the only answer provided does not have a SQL or design-based solution (i.e. it mentions OOP, inheritance, and interfaces).
I'm also kind of a novice when it comes to database design, and so I'd appreciate any insights.
It appears there are more helpful responses on Stack Overflow:
- Here
- And here
- Aaaand here (key concept being: class table inheritance)
I have referred to the solutions there and suggest others finding my question do so as well.
Despite the above-provided links, I am still on the lookout for responses here and would appreciate any solutions provided!
I am using PostgreSQL.
Best Answer
I personally don't like to use a multi-table schema for this purpose.
I've set a dbfiddle sample.
My proposed table schema:
Let me insert some data:
If you need to fetch common attributes:
Or you can easily get Clothes by Brand:
But for me, one of the best of this schema is that you can filter by Attibutes:
Using a multi-table schema whatever of the previous queries will require to deal with an unlimited number of tables, or with XML or JSON fields.
Another option with this schema, is that you can define templates, for example, you could add a new table BrandAttrTemplates. Every time you add a new record you can use a trigger or a SP to generate a set of a predefined attributes for this Branch.
I'm sorry, I'd like to extend my explanations by I think it is more clear than my English.
Update
My current answer should works on no matter which RDBMS. According to your comments, if you need to filter attributes values I'd suggest small changes.
As far as MS-Sql doesn't allow arrays, I've set up a new sample mantaining same table schema, but changing AttrValue to an ARRAY field type.
In fact, using POSTGRES, you can take advantatge of this array using a GIN index.
(Let me say that @EvanCarrol has a good knowledge about Postgres, certainly better than me. But let me add my bit.)
Now, you can additionally query using individual attributes values like:
This is the result: