[RESOLVED] to add a table or not to add a table

asucrews

that is the question....

i ran in to a issue in my plans for this site. i was trying to think of a good way search any or all 6 options i have for this site. then i relize i not but anything in there to say what vendors have what lenghts or diameters.

if thing that pop in to my head was just ad a new coloum vendor to each table witch i done on some tables, then i relize might not be best way. then next thing pop in to my head adv make a table that just stores vendorId and diameterID/lengthID. but i not sure if that best way to go.

any thoughs on what method would be better?

please feel free to point out better table Structures to any or all my tables list below

CREATE TABLE IF NOT EXISTS `colors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor` int(11) NOT NULL,
  `fiber` int(11) NOT NULL,
  `color` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=32 ;


CREATE TABLE IF NOT EXISTS `diameters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `diameter` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `units` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=13 ;


CREATE TABLE IF NOT EXISTS `ends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor` int(11) NOT NULL,
  `end` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;


CREATE TABLE IF NOT EXISTS `fibers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor` int(11) NOT NULL,
  `fiber` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=19 ;


CREATE TABLE IF NOT EXISTS `lengths` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `length` int(11) NOT NULL,
  `units` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=14 ;


CREATE TABLE IF NOT EXISTS `vendors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `website` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

cretaceous

correct - you need to add another table for the vendor : diameter matches

note it can be the same table for vendor : diameter and vendor: length

so you'll have :
vendor id

dimension id
new varible to hold if length or diameter

that should be better than adding the vendor id to each table (though hard to be 100% sure without knowing the data)
if you can get the same dimension 'thing' from different vendors you definitely need the new "match" table

asucrews

thanks that helps...the more looking in to this i going to have add the table...oh well.