Hi all, I’m new to rails and DB design also. I’ve mainly just been
hacking apps written and designed by others. At any rate, I’m having a
tough time with some DB design and Rails decisions. I’ll just layout
the situation and ask for recommendations.
Imagine a product that you can build online, like the “Build a Bear
Workshop” concept. You start with a base product and then add on
features or items. For instance, if we use the Teddy Bear example, you
can give him a coat, hat, gloves, glasses, hair, etc… My problem is
when two products conflict. I want to specify that two products
conflict even if they are not of the same type. For instance, if winter
gloves are chosen, a summer dress should not be allowed.
I want to specify products that conflict in a manual way rather than
relying on categories of things. It might not make much sense to you,
but because of the uniqueness of the product it is the best way.
So, here is the basic DB schema that comes to mind right off the bat.
CREATE TABLE accessories (
id INT NOT NULL auto_increment,
bear_type_id INT NOT NULL DEFAULT 0,
accessory_name VARCHAR(20) NOT NULL,
CONSTRAINT fk_accessories_bear_type FOREIGN KEY (bear_type_id)
REFERENCES bear_types(id),
PRIMARY KEY (id)
);
CREATE TABLE conflicts (
id INT NOT NULL auto_increment,
accessories_id_1 INT NOT NULL,
accessories_id_2 INT NOT NULL,
CONSTRAINT fk_conflicts_accessories_1 FOREIGN KEY (accessories_id)
REFERENCES accessories(id),
CONSTRAINT fk_conflicts_accessories_2 FOREIGN KEY (accessories_id)
REFERENCES accessories(id),
PRIMARY KEY (id)
);
It seems like the minimalist way to do it, although I’ve not seen
something like this before. So, the main problem as I see it is that
Rails will not automatically find the foreign keys in conflicts because
they are not named accessory_id. For all I know this is the completely
newbie and stupid way to do it, but it seems simple enough.
I’ve considered other things like using a “jump table”. In this case the
schema would look something like this.
CREATE TABLE accessories (
id INT NOT NULL auto_increment,
bear_type_id INT NOT NULL DEFAULT 0,
name VARCHAR(19) NOT NULL,
CONSTRAINT fk_accessories_bear_type FOREIGN KEY (bear_type_id)
REFERENCES bear_types(id),
PRIMARY KEY (id)
);
CREATE TABLE conflicts (
id INT NOT NULL auto_increment,
PRIMARY KEY (id)
);
CREATE TABLE accessories_conflicts (
id INT NOT NULL auto_increment,
accessory_id INT NOT NULL,
conflict_id INT NOT NULL,
CONSTRAINT fk_accessories_conflicts_accessory FOREIGN KEY
(accessory_id) REFERENCES accessories(id),
CONSTRAINT fk_accessories_conflicts_conflict FOREIGN KEY (conflict_id)
REFERENCES conflicts(id),
PRIMARY KEY (id)
);
In this way, you can use has_and_belongs_to_many, but it just seems
kinda dumb to have a table with just an id column. The other way would
be to somehow do it through has_many :through, although I still really
don’t understand that one.
So, please let me know what you think as I’ve been looking at this too
long and can’t decide or find what is the best practice.
Thanks a million!
Shagy