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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]