Instead of having one table for each category, the more relational database-y is applied here:
create table category ( id serial primary key, name text not null ); create table attribute ( id serial primary key, name text not null ); create table item ( id serial primary key, category_id integer not null references category (id), description text ); create table category_attribute ( attribute_id integer not null references attribute (id), category_id integer not null references category (id) ); create table item_attribute ( attribute_id integer not null references (attribute.id), item_id integer not null references item (id), value text );
When you create a category, you save its name (and any other individual attributes) in the category
table. You will make sure that the attribute
table has an entry for each attribute that has a category, and then use the category_attribute
table to associate these attributes with this category.
When you add a new member to the category, you use the item
table to store basic things about the item and the item_attribute
table to store the values ββof each of its attributes. Thus, given the cars and pets you mentioned, your database may look like
category id | name ----+------ 1 | car 2 | pet attribute id | name ----+------------ 1 | make 2 | breed 3 | model_year 4 | name category_attribute attribute_id | category_id --------------+------------- 1 | 1 2 | 2 3 | 1 4 | 2 item id | category_id | description ----+-------------+---------------- 1 | 1 | Hyundai Accent 2 | 2 | Fuzzy kitty item_attribute attribute_id | item_id | value --------------+---------+--------- 1 | 1 | Hyundai 3 | 1 | 2007 2 | 2 | DSH 4 | 2 | Sam
This approach may seem rather unobvious, since it does not match the "single object with many attributes" style that you use with Rails models. However, how relational databases work. I believe there is an ActiveRecord magic that you can do to make an object / relational translation a little more automatic, but I donβt remember what it called at the moment.
source share