Re: database structure

[Hiep Nguyen]

On Mon, 2 Jul 2007, Christophe Gregoir wrote:

> 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
>>>> [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
>>>> 
>>>> 
>>> 
>>> 
>> 
>> 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/mysql?unsub=1
>
>

take your advice, i looked in to JOIN and i got the idea.  but i noticed 
that in order to use JOIN, don't you need to have the same column name in 
both tables?  i just don't see it in your example here.  is there 
something that i'm missing?

can u give a select example with JOIN on three tables above?

Thanks,
T. Hiep