I've got this project that is really making me nervous. I honestly don't know if I can handle it but I'll give it my best. So here goes...please use this page as a reference:
(By the way, this relates to this thread.)
I've got a database set up with "top level" categories such as engine, transmission, undercarriage, etc. It also has subcategories; engine model, flywheel power, etc.
I need to be able to relate each of these things to a particular product (piece of equipment), in this case, a CATERPILLAR - D6N.
So for example, this product has these characteristics but another product might not use Engine>Bore. So, I'm wondering what the best way to set up the database would be. In my mind, this is pretty complex because I'm going to have to relate like 4 or 5 table to get the info that I need.
Here's the structure that I've envisioned:
# Table structure for table `tblcomp_categories`
# this is the table containing the "specs" categories such as engine, transmission, etc.
CREATE TABLE tblcomp_categories (
comp_cat_id tinyint(3) unsigned NOT NULL auto_increment,
comp_cat_name varchar(40) default NULL,
PRIMARY KEY (comp_cat_id)
# Table structure for table `tblcomp_specs`
# This would be the individual spec: bore, emgine model, flywheel power, etc.
# I also need to be able to pull one of these specs and assign it a value (in another table)
CREATE TABLE tblcomp_specs (
comp_specs_id smallint(5) unsigned NOT NULL auto_increment,
comp_specs_name varchar(75) default NULL,
fk_comp_cat_id tinyint(3) unsigned default NULL,
PRIMARY KEY (comp_specs_id)
# Table structure for table `tblproducts`
# This is the product table
CREATE TABLE tblproducts (
prod_id mediumint(8) unsigned NOT NULL auto_increment,
fk_mfr_id smallint(5) unsigned NOT NULL default '0',
prod_name varchar(50) NOT NULL default '',
fk_cat_id smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (prod_id),
KEY fk_mfr_id (fk_mfr_id,fk_cat_id)
Have I bitten off more than I can chew? Is there a better way?