RE: database structure

[Hiep Nguyen]

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
> [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
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

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