On Mon, 2 Jul 2007, Rajesh Mehrotra wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
thank you, but what happen when X change let say from 5 to 7? that means
i have to insert 2 more records into ShapeElements. what are we going to
do with Data Table?
Also, Data Table will be HUGE, isn't it?
T. Hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
