Dynamic table design

[newbie14]

Dear All,
For now I have two tables as below. The tblTyre store all the tyres.Then tblTyreConfig store the relevant tyreID in each of the column for instance fl(front left wheel),fr(front right wheel),b1LI(back first left in wheel),b1L0(back first left out),b1RI(back first right in wheel),b1R0(back first right out),b2LI(back second left in wheel),b2L0(back second left out),b2RI(back second right in wheel),b2R0(back second right out. Thus if your notice now this design cater for a vehicle where it has 10 wheel 2 in front and 8 at the back. So now this is a static design. How can I can cater for more wheel by adding more columns is it? Any idea. Thank you.

CREATE TABLE IF NOT EXISTS `tblTyre` (
  `tyreID` int(2) NOT NULL,
  `tyreSerial` int(15) NOT NULL,
  PRIMARY KEY  (`tyreID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tblTyreConfig` (
  `vehicleID` int(5) NOT NULL,
  `fl` int(2) NOT NULL,
  `fr` int(2) NOT NULL,
  `b1LI` int(2) NOT NULL,
  `b1LO` int(2) NOT NULL,
  `b1RI` int(2) NOT NULL,
  `b1R0` int(2) NOT NULL,
  `b2LI` int(2) NOT NULL,
  `b2L0` int(2) NOT NULL,
  `b2RI` int(2) NOT NULL,
  `b2R0` int(2) NOT NULL,
  PRIMARY KEY  (`vehicleID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[debasisdas]

That is not a practical design.

How different is b1R0 from that of b2LI and again from b2L0 ?

What about storing these as flags in another column in the table.

[newbie14]

Dear Deba,
I know is not practical and too rigid. What is your best suggestion then to overcome and cater for any number of wheels.

[debasisdas]

Store wheel number as a flag in another column.

[newbie14]

Dear Deba,
I dont quite understand your idea of storing wheel number as the flag in another column.

[debasisdas]

are you storing vehicle information also in the tables ?

[smantscheff]

Set up one table for vehicles, one for tyres. Add a "position" column to the tyres table. Establish a 1:n relation from vehicles to tyres. Set up a unique index on vehicle_id and position in the tyres table to avoid duplicates.

[newbie14]

Dear Smantscheff,
Do you think I should even build a position table to store the positions first. Then link into the tblTyre the positionID. What is your recommendation for the position codes should be?

[smantscheff]

Think of your material as objects. A vehicle is an object, a tyre is an object. A position is not, but a property of an object, in this case of the object tyre. So the position property (= column) belongs in the tyres table.
If you want to make sure that only a limited range of positions is used, make the position field type an enum field.
Do not use "position codes". Use legible and intellegible field content. A database is not only a technical vehicle but also a means of communication.

[debasisdas]

please try working on this model

TYRE
------------
T_ID
T_TYPE
T_BATCH_NUM
T_MANUFACTURER
T_LOT_NUM
T_DESCRIPTION
T_POSITION
…
…
VEHICLE
--------
V_ID
V_DESC
V_TYPE
T_ID1
T_ID2
T_ID3
T_ID4
…
…

[newbie14]

Dear Smantscheff,
I agree with your idea the problem is that for instance some of the vehicles got 20 or 30 wheels to then in enum I have to define all the position codes is it? Is that fine with enum with such amount of values? I mean from the mysql persepctive? Thank you.

[newbie14]

Dear Deba,
If I put it in the vehicle table again it will be waste because I have to cater for the maximum fields of the table and mostly will not be filled right.

[smantscheff]

Do not care for space, efficiency and waste. MySQL is quite capable handling all these. Optimize your design in terms of usability and legibility.
From a technical point of view, enums are one byte fields with a reference table (hidden in the table definition). Therefore you can have up to 255 enum values in one field. And it's efficient, too.

[newbie14]

Dear Smantscheff,
I will have something like this 'FL1','FR1','BL1','BR1' etc. This is fine right. So you mean I have 255 of these is it? Thank you.

[smantscheff]

Yes, you can have up to 255 of those position values in the definition of an enum field.