Re: database structure

[Hiep Nguyen]

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