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
> (516) 478-4100, 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
>
>
