structure question - many to many?

[turpentyne]

I need an extra brain to help me think this through, give me a couple extra opinions/suggestions on how to structure this database.

 

It's a "build your own" component that I'm doing for a rifle company - the challenge being that there is some tiered options.

 

I could just do a many to many set up where I have a table of the product.

 

Table_rifles :: includes ID, name, descriptors, etc.

 

Then the components :: a table for each category....

table_barrel_options , table_bolts , table_stocks, magazine, magazine_receiver.

 

Then I assume I'd do a connection_table with the columns: ID, Rifle_ID, component_category, component_ID

 

Here's where things get tricky. I have a javascript menu of options where they pick, for example, "magazine receivers"

I want to be able to query the database to only show the magazines that would fit into the magazine receiver.

 

How can I add some conditionals to these, so that I can do what I need? Is there a separate column somewhere that is my silver bullet? Or is there another way to do the whole thing? A different table? Etc

[requinix]

What do the tables for the different components look like?

[turpentyne]

Each component table is built with an autogenerated key ID in the first column, a name and a few descriptors of the item

 

ID  |  Name  |  Description |  length  |  weight  |  Make  | price

[requinix]

All different descriptors? All required?

[turpentyne]

Many of the descriptors repeat across a few tables, but some would be unique to the component. caliber is a column that might be found in the barrel, but it's irrelevant to a rifle stock.

 

hope that made sense.

 

 

 

[requinix]

Then I'd be inclined to throwing in a few tables: component, category, attributes, and attribute values.

table_rifles
* ID
* name
* description

table_rifle_components - association between rifles and their components
* ID
* rifle -> table_rifles
* component -> table_components

table_components - list of all components
* ID
* name
* description
* category -> table_component_category
* common attribute 1
* common attribute 2
* common attribute 3

table_component_categories - list of all component categories
* ID
* name

table_attributes - list of unique attributes for a component/category
* ID
* name
* category -> table_component_category

table_attribute_values - values for the unique attributes of a component
* ID
* component -> table_components
* attribute -> table_attributes
* value

If you're not sure how to read that, can you give some sample data I can demonstrate with?

[turpentyne]

So far that's making sense... it breaks it down further than I had.

 

 

One question... so if I needed to include a connection between the components themselves, is there a feasible way to catalog the relation between siblings?

 

for example:

riflestock A  is compatible with trigger A and trigger C

But

riflestock B is compatible with trigger B and Trigger C

 

 

Do I just need to build a new table:

 

table_component-to-component 

*  ID

*  component ID -> table_components

*  compatible component id??? -> table_components

 

does that make sense? Or would i use the table_component_categories to include this data?

 

I could mock up some data, but the client hasn't given me any thing yet for real-world information. I'm trying to get the logic all figured out so I'm ready.

 

 

[requinix]

That's the most popular way, but it's a hassle since you always have to check two columns in the table. And it makes JOINs quite a headache.

 

Another would be to maintain a table of compatibility "groups", then the group recorded with the component directly.

table_component_compatibilities
* ID
* maybe a name or description but not much else

table_components
* compatibility group, or NULL for incompatible