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
[EMAIL PROTECTED]
-----Original Message-----
From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Monday, July 02, 2007 8:53
AM
To: mysql@lists.mysql.com
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
