Re: database structure

[Christophe Gregoir]

CREATE TABLE `tags` (`tagid` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY 
KEY, `location` VARCHAR(255) NOT NULL DEFAULT '') ENGINE=INNODB;
CREATE TABLE `dimension_type` (`id` ..., `type` VARCHAR(255) NOT NULL 
DEFAULT '') ENGINE=INNODB;
CREATE TABLE `tags_shape_dimensions` (`tag` INT(11) UNSIGNED, `dim` 
INT(11) UNSIGNED, `value` VARCHAR(255) NOT NULL DEFAULT '') ENGINE=INNODB;

1) You fill the dimension_type table with all the possible 
characteristics (in fact, characteristics would be a better name for 
that table) you will be using, with the advantage of being able to very 
easily add an extra characteristic later down the road.
2) You fill the tags table with all your differenent tags and locations.
3) You fill in the tags_shape_dimensions table with your tag and any 
characteristic that applies to it and its associated value.

You would select data from these tables by using JOIN's. A bit difficult 
to grasp if you're an absolute beginner, but you'll never want to go 
back afterwards.

Greetz,

boro

Hiep Nguyen schreef:
> On Mon, 2 Jul 2007, Borokov Smith wrote:
>
>> Or:
>>
>> Tag
>> ShapeDimension (type enum('height', 'thickness', etc), value 
>> VARCHAR() or INT())
>> TagsShapeDimensions (FOREIGN KeY TAG, FOREIGN KEY ShapeDimension)
>>
>> 1 less table
>>
>> Greetz,
>>
>> boro
>>
>>
>> Rajesh Mehrotra schreef:
>>> Hi,
>>>
>>> You can do this in four tables:
>>> 1. Tag
>>> 2. Shape (with an additional field, let us call it X, describing how
>>> many data elements each shape has)
>>> 3. ShapeElements : one record describing each data element (length,
>>> width etc.) for each shape. Record count for each shape: X
>>> 4. Data Table : "X" number of records for each TagID. References
>>> ShapeElements.
>>>
>>> The number of table will remain fixed at four, no matter how many 
>>> shapes
>>> you have. And your SQL statements will be generic, most of the times,
>>> regardless of the shape.
>>>
>>> Sincerely,
>>>
>>> Raj Mehrotra
>>> hccs - Experts in Healthcare Learning
>>> [phone], x105
>>> raj@stripped
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: Hiep Nguyen [mailto:hiep@stripped] Sent: Monday, July 02, 
>>> 2007 8:53 AM
>>> To: mysql@stripped
>>> Subject: database structure
>>>
>>> Hi all,
>>>
>>> i'm seeking for help/suggestion on how to create relationship for this
>>> scenario:
>>>
>>> I have one table (tag) with the following fields:
>>>
>>> tagid, location, weight, grade, heat, shape, diameter, length, width,
>>> height, ... (and many other fields)
>>>
>>>
>>> what i want to do is move the shape field into a different table,
>>> however, each shape will have different dimensions
>>>
>>> for sample:
>>>
>>> rod bar has diamter and length, but square/flat bar has width, 
>>> height (thickness), and length.  wire has only diameter.
>>>
>>> but i also don't want to create a huge table to hold all the 
>>> possible fields in all shapes, so i'm thinking create a table per 
>>> shape:
>>>
>>> round table (diameter, length)
>>> square table (width, height, length)
>>> i-beam table (flange height, flange thickness, web thickness)
>>> angle table (leg1, leg2, thickness)
>>> ...
>>> and so on.
>>>
>>> in turn, i have 2 tables: tag, shape + as many table as there are 
>>> shapes
>>>
>>> my question is: is there a better to do this?  how do i query with 
>>> this structure? i'm a bit confuse on how to manipulate data if each 
>>> shape
>>> have diffent fields???
>>>
>>> Thank you for all your help (sorry for a long email)
>>> T. Hiep
>>>
>>>
>>
>>
>
> i'm a novice and confused, can you enlight a little bit more? example 
> of data if possible.
>
> thanks,
> T. Hiep
>