Re: database structure

Mon, 02 Jul 2007 07:04:27 -0700

CREATE TABLE `tags` (`tagid` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, `location` VARCHAR(255) NOT NULL DEFAULT '') ENGINE=INNODB; CREATE TABLE `dimension_type` (`id` ..., `type` VARCHAR(255) NOT NULL DEFAULT '') ENGINE=INNODB; CREATE TABLE `tags_shape_dimensions` (`tag` INT(11) UNSIGNED, `dim` INT(11) UNSIGNED, `value` VARCHAR(255) NOT NULL DEFAULT '') ENGINE=INNODB;
1) You fill the dimension_type table with all the possible characteristics (in fact, characteristics would be a better name for that table) you will be using, with the advantage of being able to very easily add an extra characteristic later down the road. 
2) You fill the tags table with all your differenent tags and locations.
3) You fill in the tags_shape_dimensions table with your tag and any characteristic that applies to it and its associated value. 


You would select data from these tables by using JOIN's. A bit difficult 
to grasp if you're an absolute beginner, but you'll never want to go 
back afterwards.


Greetz,

boro

Hiep Nguyen schreef:

On Mon, 2 Jul 2007, Borokov Smith wrote:


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
[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








i'm a novice and confused, can you enlight a little bit more? example 
of data if possible.


thanks,
T. Hiep




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]























					Reply via email to