Re: database structure

[Borokov Smith]

Hey there again,

I suggest you look up a tutorial about database normalisation. Good ones 
are often hard to find.


In general, you give all tables that contain data you will be 
referencing in other tables a numeric id (INT(11) UNSIGNED) as primary 
key, and you use that key as the constraint.

Your table `locations` will be referenced in your table `tags` as such:
FOREIGN KEY (`location`) REFERENCES `locations`(`id`) ON UPDATE CASCADE 
ON DELETE SET NULL
Search the mysql manual for those update/delete options to see what they 
do and adjust them to your needs. The ones given are the ones I in most 
situations.


HTH,

boro


Hiep Nguyen schreef:

Now, if I have a location table with id, name, address, phone, fax, etc...

Should I put id or name into the tag table?

If id used, then how do i look up the name, address, phone, fax, etc... when
I do a select on tag table?

Thank you for all your helps
T. Hiep
-Original Message-
From: Mogens Melander [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 02, 2007 3:45 PM

To: mysql@lists.mysql.com
Subject: Re: database structure


On Mon, July 2, 2007 21:10, Hiep Nguyen wrote:
  

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?



Using the form:

select t1.field1 data1, t2.field1 data2, t3.fieldn data3
from table_a t1
left join table_b t2 on ( t1.id=t2.t1_ref )
left join table_n t3 on ( t2.id=t3.t2_ref );

You can join on allmost anything.

?? Can typecasts be used in this scenario ??

  

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



  



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

RE: database structure

[Hiep Nguyen]

Now, if I have a location table with id, name, address, phone, fax, etc...

Should I put id or name into the tag table?

If id used, then how do i look up the name, address, phone, fax, etc... when
I do a select on tag table?

Thank you for all your helps
T. Hiep
-Original Message-
From: Mogens Melander [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 02, 2007 3:45 PM
To: mysql@lists.mysql.com
Subject: Re: database structure


On Mon, July 2, 2007 21:10, Hiep Nguyen wrote:
>
> 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?

Using the form:

select t1.field1 data1, t2.field1 data2, t3.fieldn data3
from table_a t1
left join table_b t2 on ( t1.id=t2.t1_ref )
left join table_n t3 on ( t2.id=t3.t2_ref );

You can join on allmost anything.

?? Can typecasts be used in this scenario ??

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

-- 
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

Re: database structure

[Mogens Melander]

On Mon, July 2, 2007 21:10, Hiep Nguyen wrote:
>
> 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?

Using the form:

select t1.field1 data1, t2.field1 data2, t3.fieldn data3
from table_a t1
left join table_b t2 on ( t1.id=t2.t1_ref )
left join table_n t3 on ( t2.id=t3.t2_ref );

You can join on allmost anything.

?? Can typecasts be used in this scenario ??

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

-- 
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

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
(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]




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


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

Re: database structure

[Christophe Gregoir]

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]

Re: database structure

[Hiep Nguyen]

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]

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
(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]

Re: database structure

[Borokov Smith]

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

  



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

RE: database structure

[Rajesh Mehrotra]

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]

Re: database structure question...

[David Bevan]

You may want to look into normalization to keep
everything organized.  However if you want pure query
speed, you will have to de-normalize some of your
tables to get optimum speed.

Have a look here for info on normalization:
http://databases.about.com/od/specificproducts/a/normalization.htm

HTH
--- bruce <[EMAIL PROTECTED]> wrote:

> hi...
> 
> i'm considering an app where i'm going to parse a
> lot of colleges (~1000)
> faculty information. would it be better to have all
> the faculty information
> in one large table or would it be better/faster to
> essentially place each
> college in it's own separate table, and reference
> each table by a
> college_ID, that's unique and assigned to each
> college, and maintained in a
> master_collegeTBL...
> 
> thoughts/comments/etc
> 
> i'm leaning towards the side that keeps each college
> information separate,
> although this means that i essentially have to deal
> with 1000s of
> tables/files...
> 
> -bruce
> [EMAIL PROTECTED]
> 
> 
> > 
> -- 
> 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]

Re: database structure question...

[Ian Sales (DBA)]

bruce wrote:


hi...

i'm considering an app where i'm going to parse a lot of colleges (~1000)
faculty information. would it be better to have all the faculty information
in one large table or would it be better/faster to essentially place each
college in it's own separate table, and reference each table by a
college_ID, that's unique and assigned to each college, and maintained in a
master_collegeTBL...

thoughts/comments/etc

i'm leaning towards the side that keeps each college information separate,
although this means that i essentially have to deal with 1000s of
tables/files...

 

- don't split data into separate tables by location. Seperate it by the 
type of information.


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


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

Re: database structure question...

[Mir Islam]

You have not said what type of information you will be storing in this
database. Is it going to be just faculty information? Even if it is
just faculty information, you do realize that each school treats
departments a bit dfferently. The faculyt maybe under different
school, may specialize in certain field(s). What about all those data?
Or are you going strictly for First, Last, College type simple
scenario?

On 7/7/05, Daniel Kasak <[EMAIL PROTECTED]> wrote:
> bruce wrote:
> 
> >even though this might mean i get a table with 5 million records??? as
> >opposed to say a 1000 different tables, each with 50,000 records?
> >
> >-bruce
> >
> >
> That's right.
> Databases are made for this sort of thing.
> If you have a separate table for each location, constructing queries to
> pull data from a number of them at once will be an absolute nightmare,
> not to mention what will happen if you have to modify the table structure.
> For example, what do you do if you want to see all records that were
> entered yesterday? You run 1000 separate queries! You can bet that this
> will be slower than if everything was in 1 table.
> Seriously, put everything in 1 table.
> 
> --
> Daniel Kasak
> IT Developer
> NUS Consulting Group
> Level 5, 77 Pacific Highway
> North Sydney, NSW, Australia 2060
> T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
> email: [EMAIL PROTECTED]
> website: http://www.nusconsulting.com.au
> 
> --
> 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]

Re: database structure question...

[Daniel Kasak]

bruce wrote:

>even though this might mean i get a table with 5 million records??? as
>opposed to say a 1000 different tables, each with 50,000 records?
>
>-bruce
>  
>
That's right.
Databases are made for this sort of thing.
If you have a separate table for each location, constructing queries to
pull data from a number of them at once will be an absolute nightmare,
not to mention what will happen if you have to modify the table structure.
For example, what do you do if you want to see all records that were
entered yesterday? You run 1000 separate queries! You can bet that this
will be slower than if everything was in 1 table.
Seriously, put everything in 1 table.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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

RE: database structure question...

[bruce]

as of now.. i've heard that there might be a file limit.. but given that i'm
using linux, i doubt it.. and if there is a limiit.. i'm sure it's a kernel
option that i can tweek...

in my app, i'm not worried about profs transferring between schools... that
data's going to be ptreety static, and separate between schools.. but i
haven't heard anyone talk to the issue of timing, with regards to doing
queries/selects/etc...

although, i can imagine the kind of query that might stretch across multiple
tables (>10) might get to be painful... on the other hand, if i have all the
information in a single table and need to make a change to the table, i'd
have to move around/modify/deal with a serious number of records, whereas,
if the college data is in separate tables, it would make changes alot
easier

or, i could do a hybrid solution if performing actual queries makes sense..
i could have a 'temp' master collegeTBL that contains all the information,
and this table is comprised of the smaller separate collegeTBLS, and i could
simply make any changes to the smaller tbls, and rebuild the master table
from time to time...

hmmm

-bruce


-Original Message-
From: Devananda [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 07, 2005 6:07 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: database structure question...


I would strongly recommend creating one table, with a column that stores
the college_ID for each faculty member, and a separate table to
correlate college name and college_id. For example...

Create table faculty (
last_name varchar(50),
first_name varchar(50),
college_id int,
primary key (last_name, first_name),
key c_id (college_id)
);

Create table colleges (
college_name varchar(50),
college_id int,
primary key (college_id)
);

This sort of structure will allow you to easily and quickly retrieve all
faculty for a given college (select last_name, first_name from faculty
where college_id="$id"). Also, if a faculty member were to be
transferred to another college w/in your system, it is easy to update
(update faculty set college_id="$new_college" where last_name="Smith"
and first_name="John"). Or, to find what college a given faculty member
is at, (select college_id from faculty where last_name="Smith" and
first_name="John"). Finding a faculty member from ~1,000 tables would be
very, very painful, not to mention slow.

Another reason not to store each college in its own table is that on
many file systems, there is a limit to the number of files allowed
within one directory, regardless of how small the files are. I believe
that on most linux's, it is in the tens of thousands. Not likely to be
reached, but if your application grew to encompass tens of thousands of
colleges, you would eventually run out of room. (See
http://answers.google.com/answers/threadview?id=122241 for an explanation.)


~ Devananda



bruce wrote:

> hi...
>
> i'm considering an app where i'm going to parse a lot of colleges (~1000)
> faculty information. would it be better to have all the faculty
information
> in one large table or would it be better/faster to essentially place each
> college in it's own separate table, and reference each table by a
> college_ID, that's unique and assigned to each college, and maintained in
a
> master_collegeTBL...
>
> thoughts/comments/etc
>
> i'm leaning towards the side that keeps each college information separate,
> although this means that i essentially have to deal with 1000s of
> tables/files...
>
> -bruce
> [EMAIL PROTECTED]
>
>
>
>
> 
>
>


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

Re: database structure question...

[Devananda]

I would strongly recommend creating one table, with a column that stores 
the college_ID for each faculty member, and a separate table to 
correlate college name and college_id. For example...


Create table faculty (
last_name varchar(50),
first_name varchar(50),
college_id int,
primary key (last_name, first_name),
key c_id (college_id)
);

Create table colleges (
college_name varchar(50),
college_id int,
primary key (college_id)
);

This sort of structure will allow you to easily and quickly retrieve all 
faculty for a given college (select last_name, first_name from faculty 
where college_id="$id"). Also, if a faculty member were to be 
transferred to another college w/in your system, it is easy to update 
(update faculty set college_id="$new_college" where last_name="Smith" 
and first_name="John"). Or, to find what college a given faculty member 
is at, (select college_id from faculty where last_name="Smith" and 
first_name="John"). Finding a faculty member from ~1,000 tables would be 
very, very painful, not to mention slow.


Another reason not to store each college in its own table is that on 
many file systems, there is a limit to the number of files allowed 
within one directory, regardless of how small the files are. I believe 
that on most linux's, it is in the tens of thousands. Not likely to be 
reached, but if your application grew to encompass tens of thousands of 
colleges, you would eventually run out of room. (See 
http://answers.google.com/answers/threadview?id=122241 for an explanation.)



~ Devananda



bruce wrote:


hi...

i'm considering an app where i'm going to parse a lot of colleges (~1000)
faculty information. would it be better to have all the faculty information
in one large table or would it be better/faster to essentially place each
college in it's own separate table, and reference each table by a
college_ID, that's unique and assigned to each college, and maintained in a
master_collegeTBL...

thoughts/comments/etc

i'm leaning towards the side that keeps each college information separate,
although this means that i essentially have to deal with 1000s of
tables/files...

-bruce
[EMAIL PROTECTED]









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

RE: database structure question...

[bruce]

even though this might mean i get a table with 5 million records??? as
opposed to say a 1000 different tables, each with 50,000 records?

-bruce



-Original Message-
From: Daniel Kasak [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 07, 2005 5:34 PM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: database structure question...


bruce wrote:

>hi...
>
>i'm considering an app where i'm going to parse a lot of colleges (~1000)
>faculty information. would it be better to have all the faculty information
>in one large table or would it be better/faster to essentially place each
>college in it's own separate table, and reference each table by a
>college_ID, that's unique and assigned to each college, and maintained in a
>master_collegeTBL...
>
>thoughts/comments/etc
>
>i'm leaning towards the side that keeps each college information separate,
>although this means that i essentially have to deal with 1000s of
>tables/files...
>
>-bruce
>[EMAIL PROTECTED]
>
>
>
You're *far* better off putting everything in one table and using a
field in the table, for example CollegeID, to identify which column
you're dealing with.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au


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

Re: database structure question...

[Daniel Kasak]

bruce wrote:

>hi...
>
>i'm considering an app where i'm going to parse a lot of colleges (~1000)
>faculty information. would it be better to have all the faculty information
>in one large table or would it be better/faster to essentially place each
>college in it's own separate table, and reference each table by a
>college_ID, that's unique and assigned to each college, and maintained in a
>master_collegeTBL...
>
>thoughts/comments/etc
>
>i'm leaning towards the side that keeps each college information separate,
>although this means that i essentially have to deal with 1000s of
>tables/files...
>
>-bruce
>[EMAIL PROTECTED]
>
>  
>
You're *far* better off putting everything in one table and using a
field in the table, for example CollegeID, to identify which column
you're dealing with.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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

RE: Database structure

[Donny Simonton]

Here are a few examples of my tables.  Table name, # of records, type, and
size.  The database currently has 898 million records in it and it's right
over 100 gigs.

Phrase49,769,178   MyISAM5.3 GB
Volume9,671,996   MyISAM1.1 GB
Word7,790,076   MyISAM942.2 MB
WordMagic128,881,167   MyISAM6.0 GB
WordMagicScores111,060,572   MyISAM7.4 GB
WordWatcher44,270,528   MyISAM4.3 GB
WordPhrases11,154,414   MyISAM450.9 MB
WordRelated13,685,867   MyISAM2.7 GB
WordRelated213,194,313   MyISAM2.6 GB
WordScore68,437,613   MyISAM12.7 GB
WordScoreTemp118,723,375   MyISAM25.3 GB
WordSearch188,769,835   MyISAM11.5 GB
WordStem15,623,221   MyISAM417.3 MB


Donny

> -Original Message-
> From: Brent Baisley [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 11, 2004 10:09 AM
> To: Ronan Lucio
> Cc: [EMAIL PROTECTED]
> Subject: Re: Database structure
> 
> Where did you read that 25 million records would be a problem? I've
> heard of people with billions of records in one table. The only
> question would be performance, but indexes would largely take care of
> that. You may run into issues with the physical size of the table and
> the underlying OS not being able to create a large enough file (i.e.
> 4GB). This is a problem with the OS, not MySQL. InnoDB would allow you
> to work around file size limitation in the OS by splitting the database
> into separate files.
> 
> 
> On May 11, 2004, at 10:10 AM, Ronan Lucio wrote:
> 
> > Hi,
> >
> > I´m working in a project of a database that should be grow
> > to more than 25,000,000 of clients.
> >
> > For all I´ve read in MySQL manual pages it´s too much records
> > to place in only one table.
> > So, my main doubt is how to divide it.
> >
> > I divide the client table in few tables according with the different
> > kinds of clients.
> > Even getting some duplicated records and getting some difficulties
> > importing and exporting clients from one table to another it should
> > take the database load cooler.
> >
> > But, I think I´ll need to place all logins and access levels in the
> > same
> > table.
> >
> > Would it be a problem?
> > Any idea how can I deal with it?
> >
> > I´m thinking to use InnoDB tables.
> >
> > Thanks,
> > Ronan
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> --
> Brent Baisley
> Systems Architect
> Landover Associates, Inc.
> Search & Advisory Services for Advanced Technology Environments
> p: 212.759.6400/800.759.0577
> 
> 
> --
> 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]

Re: Database structure

[Brent Baisley]

Where did you read that 25 million records would be a problem? I've 
heard of people with billions of records in one table. The only 
question would be performance, but indexes would largely take care of 
that. You may run into issues with the physical size of the table and 
the underlying OS not being able to create a large enough file (i.e. 
4GB). This is a problem with the OS, not MySQL. InnoDB would allow you 
to work around file size limitation in the OS by splitting the database 
into separate files.

On May 11, 2004, at 10:10 AM, Ronan Lucio wrote:

Hi,

I´m working in a project of a database that should be grow
to more than 25,000,000 of clients.
For all I´ve read in MySQL manual pages it´s too much records
to place in only one table.
So, my main doubt is how to divide it.
I divide the client table in few tables according with the different
kinds of clients.
Even getting some duplicated records and getting some difficulties
importing and exporting clients from one table to another it should
take the database load cooler.
But, I think I´ll need to place all logins and access levels in the 
same
table.

Would it be a problem?
Any idea how can I deal with it?
I´m thinking to use InnoDB tables.

Thanks,
Ronan


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


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Database structure

[beacker]

> The schema is :
> Patients(#patient_nr,name,etc...)
> Assessment(#assessment_nr, #patient_nr, assessment_type, other usefull
> values).
> Assessment_types(assessment_type, labtest_nr)
> An assessment is composed of different tests, let's say assessment type 1
> is
> composed of lab test 1,2,3,5 and assessment type 2 of lab test number 10
> to 70.

Looking at the two alternatives, the second based upon a normalization
of patient with assessments fits closer with standard medical identification.
The assessments are usually based upon the ICD-9 coding.  As an example
191.3 is a Neoplasm of the brain, Parietal lobe.

  There are corresponding elements for procedures ala 01.2 is a Craniotomy
and craniectomy code with subsequent digits further describing the particular
procedure.  Using these codes provides an industry standard mechanism.

Brad Eacker ([EMAIL PROTECTED])



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

Re: Database structure

[Daniel Clark]

I used to program for a medical tester.

I used method 2:
  2 - a table with 60 rows for one assessment :
  results(#assessment_nr, labtest_nr, p, d) where p and d are my two
  results.

The BIG advantage was changes to the tests, adding new ones, or deleting
fields.   Made it much more flexible.


> I would like an advise on the following problem :
>
> I have a table of patients.
> Each patient can make different biological assessments.
> Each assessment is always decomposed into different laboratory tests.
> A laboratory test is made of a test number and two values coming from
> analysers.
>
> The schema is :
> Patients(#patient_nr,name,etc...)
> Assessment(#assessment_nr, #patient_nr, assessment_type, other usefull
> values).
> Assessment_types(assessment_type, labtest_nr)
> An assessment is composed of different tests, let's say assessment type 1
> is
> composed of lab test 1,2,3,5 and assessment type 2 of lab test number 10
> to 70.
>
> I have an assessment with 60 different lab tests (always the same). I have
> two ways
> for storing the values :
>
> 1 - a table with 120 columns for the two values.
> results(#assessment_nr, p10,d10, p11,d11, .,p70,d70).
> where 10 to 70 represents the lab test number.
>
> 2 - a table with 60 rows for one assessment :
> results(#assessment_nr, labtest_nr, p, d) where p and d are my two
> results.
>
> Here comes my question. Which of the two would you choose?
>
> The firsrt solution has the advantage of returning one single row for one
> complete
> assessment. If I have to make statistics, it is easy. But, if I have to
> modify the
> composition of an assessment (which occurs very rarely), I shall have to
> use an alter
> table instruction. As I have 4 different assessment types, I have to
> create five
> different tables, one per assessment type.
>
> The second solution is normalized and more elegant. But I am preoccupied
> by the
> size of the table. For one assessment, I'll store 60 rows with only two
> useful integers
> in it. And you must add the size of the index. With 25.000 assessments a
> year, it
> makes 1.500.000 rows with only 4 columns amoung them 2 only for the
> results and 2
> for identification. I would like to store 10 years online, so 15.000.000
> rows. What
> about the size of index ?
>
> Any advise ? I thank you in advance.
>
>
> Alain Reymond
>
> (I hope that it is clear enough with my bad English).
>
>
> --
> 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]

Re: Database Structure

[Frank Gates]

Stephanie,

Without knowing the architecture of your program I cannot give a precise
answer.  But I'll make a few stabs at it...

First, MySQL, being an application in its own right, can be installed
separately from your application.  Alternately, your application's
installation program could include calling the MySQL installer.  The user
could bypass that step if it has already been installed.  Future upgrades of
your program would not require MySQL to be reinstalled, though the tables and
databases it uses may need refreshing.

Second, MySQL is network-based, so that you may have MySQL installed on one or
more machines and your application installed on other machines.  It would
require as part of the installation process that the IP address for the MySQL
server, its port, username, password, etc. be supplied.


Hope this helps,

Frank


Stephanie Piet wrote:

> Does anyone know if there's a way in MySQL have the same functionality
> without installing the whole program on a users machine? I'm using a Java
> program along with Connector/J and a MySQL DB. We are trying to make it so
> the user doesn't have to install MySQL everytime they want to install the
> program on their machine.
>
> Thanks,
> Stephanie
>
> -
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"; before
> posting. To request this thread, e-mail [EMAIL PROTECTED]
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail [EMAIL PROTECTED] instead.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Database Structure

[Leo Przybylski]

Stephanie,

Connector/J takes advantage of client/server architecture over TCP. This
means that the MySQL server could exist on one machine (a server), and
the client application(s) can connect to it via TCP and Connector/J from
anywhere that has a TCP route to the MySQL server.

If Connector/J is being used in the application then the answer is, no,
you do not have to install MySQL on the client machine unless the client
machine is also the server machine.

-Leo
On Thu, 2002-10-10 at 05:59, Stephanie Piet wrote:
> Does anyone know if there's a way in MySQL have the same functionality
> without installing the whole program on a users machine? I'm using a Java
> program along with Connector/J and a MySQL DB. We are trying to make it so
> the user doesn't have to install MySQL everytime they want to install the
> program on their machine.
> 
> 
> Thanks,
> Stephanie
> 
> 
> -
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"; before
> posting. To request this thread, e-mail [EMAIL PROTECTED]
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail [EMAIL PROTECTED] instead.
> 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Database Structure

[David Lloyd]

Stephanie,

> Does anyone know if there's a way in MySQL have the same functionality
> without installing the whole program on a users machine? I'm using a Java
> program along with Connector/J and a MySQL DB. We are trying to make it so
> the user doesn't have to install MySQL everytime they want to install the
> program on their machine.

Somewhere on the MySQL (query) site I saw something about embedded
mysql...


DSL
-- 
Somewhere where I hope to find someone
 Still I find myself near you
 If I hear the words I'll go.
(from the musical Martin Guerre)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Database Structure

[Francisco Reinaldo]

How are you connecting to MySQL?, through MySQL ODBC I
am assuming. I might happen that the ODBC driver for
MySQL does not implement ADOX functionality.

Have you tried to take a look to the MySQL++ API? You
might have to create your own dll (or COM component)
to modify the database structure from your
application.

Good Luck!
--- Efren Pedroza <[EMAIL PROTECTED]> wrote:
> Hi everyone,
> 
> I just become a MySQL user and I am trying to
> migrate my application from
> Access 2000 / SQL Server to MySQL, my system was
> developed with MS Visual
> Basic 6 SP5, everything seems to be Ok, but  in
> my application I modify
> often Database Structure using ADOX to retrive
> structure and modify it, but
> when the program try to retrive the structure
> nothing works, how can I get
> structure database from VB ?
> 
> Is there a way to do that ?
> 
> Or maybe I've to try with some tool that I don't
> known.
> 
> Any comment or suggestion is welcomed !!!
> 
> Thanks in advance !!!
> 
> 
>
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list
> archive)
> 
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> 


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: database structure script

[Todd Williamsen]

I should have been more clear...

http://www.mysql.com/doc/m/y/mysqldump.html

-Original Message-
From: Todd Williamsen [mailto:[EMAIL PROTECTED]] 
Sent: Sunday, February 24, 2002 10:03 PM
To: 'Pax'; 'MySQL'
Subject: RE: database structure script


Yes..  Use the DUMP command

-Original Message-
From: Pax [mailto:[EMAIL PROTECTED]] 
Sent: Sunday, February 24, 2002 9:25 PM
To: 'MySQL'
Subject: database structure script


Is there an easy way to create SQL script from existing database and use
it to create a new database? 

Tks
Pax



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: database structure script

[Todd Williamsen]

Yes..  Use the DUMP command

-Original Message-
From: Pax [mailto:[EMAIL PROTECTED]] 
Sent: Sunday, February 24, 2002 9:25 PM
To: 'MySQL'
Subject: database structure script


Is there an easy way to create SQL script from existing database and use
it to create a new database? 

Tks
Pax



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: database structure script

[Doug Thompson]

Yes.  You want to use mysqldump.

http://www.mysql.com/doc/m/y/mysqldump.html


On Sun, 24 Feb 2002 22:24:39 -0500, Pax wrote:

>Is there an easy way to create SQL script from existing database and use
>it to create a new database? 
>
>Tks
>Pax




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Database structure DIFF

[Jan Dvorak]

This way madness lay.

I you don't have data in the database, then it's easy:
Just drop the old database, and use your new script to create it again.
The end result is the same as if you have used alter table's.

O.k., but you probably do have data in the database that you want to preserve.
But then, it's not a well-posed problem, as mathematicians would say.
The real world equivalent of this is that whichever solution you pick up,
you'll be flamed.

Assume just a very simple difference:
Database D1 have table T1 with columns C1, C2.
Database D2 have table T1 with columns C1, C2, C3.
You'll leap up and say: so the difference is the addition of C3.
But, couldn't it be that you decided to rename C2 into C3 and add another C2?
How are you going to decide which column is new and which column was there already?
By column names? By data types? By the combination of those two?
Who has the right answer?

Most important though, you didn't say what should happen to the _data_
in the columns - and you can't say that from just looking 
at the database structures.
Even if you settle on a way to decide which column is really new,
how do you know what should be put in a new column?
A default value?  You probably didn't create an extra column to hold just a default 
value.
So you need to do some extra work in order to assign the right values to the new 
column.


O.k., so the statement is: 
if you don't have data in it, why not drop it and create it anew.
And if you do have data in the database, creating a mapping script
from just the difference of the database structures is bound to go wrong.

Jan


[EMAIL PROTECTED] wrote:
> 
> Hello Cal,
> 
> I don't think there is a "magic bullet" for what you want.  Although I'm sure some 
>sort of Perl script could do a comparison and create the proper ALTER TABLE SQL to 
>make the two tables equal.
> 
> I usualy just copy tables using the CREATE TABLE syntax (as below) and make the 
>manual alterations as needed.
> 
> Regards,
> 
> - Scott
> 
> > Database in Test:
> >
> > Table1
> > 
> > Table1ID
> > 
> > myField1 varChar(20)
> > myField2 int
> > myFiled3 varChar(40)
> > 
> >
> > Database in Prod:
> > Table1
> > 
> > Table1ID
> > 
> > myField1 varChar(60)
> > myField2 int
> > myField3 varChar(40)
> > myField4 int
> > 
> >
> > I'd like something that compares these two databases. (Either the databases
> > themselves or a mysqldump of the databases and comes up with a sql script:
> >
> > Alter table Table1 add myField4 int;
> > Alter table Table1 change myField1 myField1 varChar(60);
> >
> > Yes, I can do it by eyeballing it...I'm just too lazy.  :)
> >
> > Thanks for replying. Hope I've made things clearer.
> >
> > Cal
> > http://www.calevans.com
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> > Sent: Sunday, January 21, 2001 1:48 PM
> > To: Cal Evans
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: Database structure DIFF
> >
> >
> >
> > I'm not sure what you mean by "structural changes".
> >
> > If the the two table have the same number of columns and they are in the
> > same order, you can do something like what I have explained below.  It you
> > included the table info from prod and test I could help you better (DESC
> > prod;).
> >
> >
> >
> > # Create the empty shell of test (all column definitions);
> >
> > CREATE TABLE prod_new
> > SELECT * FROM test
> > WHERE 1=2;
> >
> > # populate prod_new with prod data
> > INSERT INTO TABLE prod_new
> > SELECT * FROM prod;
> >
> > - Verify the prod_new is correct
> > - DROP prod
> > - RENAME prod_new to prod.
> >
> >   - Scott
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > > Pardon the newbie question but is there a tool available for MySQL that I
> > > can give it 2 databases and it give me the ALTER TABLE statements
> > necessary
> > > to make 2 identical to 1?
> > >
> > > Specifically, I have a test and prod database.  I've made structural
> > changes
> > > to test and want to propagate them to prod.  Is there an easy way to do
> > > this?
> > >
> > > TIA,
> > > Cal
> > >
> > > Cal
> > > http://www.calevans.com
> > >
> > >
> > >
> > > ---

RE: Database structure DIFF

[scott]

Hello Cal,

I don't think there is a "magic bullet" for what you want.  Although I'm sure some 
sort of Perl script could do a comparison and create the proper ALTER TABLE SQL to 
make the two tables equal.

I usualy just copy tables using the CREATE TABLE syntax (as below) and make the manual 
alterations as needed.

Regards,

- Scott


> Database in Test:
> 
> Table1
> 
> Table1ID
> 
> myField1 varChar(20)
> myField2 int
> myFiled3 varChar(40)
> 
> 
> Database in Prod:
> Table1
> 
> Table1ID
> 
> myField1 varChar(60)
> myField2 int
> myField3 varChar(40)
> myField4 int
> 
> 
> I'd like something that compares these two databases. (Either the databases
> themselves or a mysqldump of the databases and comes up with a sql script:
> 
> Alter table Table1 add myField4 int;
> Alter table Table1 change myField1 myField1 varChar(60);
> 
> Yes, I can do it by eyeballing it...I'm just too lazy.  :)
> 
> Thanks for replying. Hope I've made things clearer.
> 
> Cal
> http://www.calevans.com
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, January 21, 2001 1:48 PM
> To: Cal Evans
> Cc: [EMAIL PROTECTED]
> Subject: Re: Database structure DIFF
> 
> 
> 
> I'm not sure what you mean by "structural changes".
> 
> If the the two table have the same number of columns and they are in the
> same order, you can do something like what I have explained below.  It you
> included the table info from prod and test I could help you better (DESC
> prod;).
> 
> 
> 
> # Create the empty shell of test (all column definitions);
> 
> CREATE TABLE prod_new
> SELECT * FROM test
> WHERE 1=2;
> 
> # populate prod_new with prod data
> INSERT INTO TABLE prod_new
> SELECT * FROM prod;
> 
> - Verify the prod_new is correct
> - DROP prod
> - RENAME prod_new to prod.
> 
>   - Scott
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> > Pardon the newbie question but is there a tool available for MySQL that I
> > can give it 2 databases and it give me the ALTER TABLE statements
> necessary
> > to make 2 identical to 1?
> >
> > Specifically, I have a test and prod database.  I've made structural
> changes
> > to test and want to propagate them to prod.  Is there an easy way to do
> > this?
> >
> > TIA,
> > Cal
> >
> > Cal
> > http://www.calevans.com
> >
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> >
> --
> --
>Scott A. Gerhardt  P.Geo.
>Gerhardt Information Technologies
>[EMAIL PROTECTED]
> --
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 


--
--
   Scott A. Gerhardt  P.Geo.
   Gerhardt Information Technologies
   [EMAIL PROTECTED]
--


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Database structure DIFF

[Cal Evans]

Database in Test:

Table1

Table1ID

myField1 varChar(20)
myField2 int
myFiled3 varChar(40)


Database in Prod:
Table1

Table1ID

myField1 varChar(60)
myField2 int
myField3 varChar(40)
myField4 int


I'd like something that compares these two databases. (Either the databases
themselves or a mysqldump of the databases and comes up with a sql script:

Alter table Table1 add myField4 int;
Alter table Table1 change myField1 myField1 varChar(60);

Yes, I can do it by eyeballing it...I'm just too lazy.  :)

Thanks for replying. Hope I've made things clearer.

Cal
http://www.calevans.com


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Sunday, January 21, 2001 1:48 PM
To: Cal Evans
Cc: [EMAIL PROTECTED]
Subject: Re: Database structure DIFF



I'm not sure what you mean by "structural changes".

If the the two table have the same number of columns and they are in the
same order, you can do something like what I have explained below.  It you
included the table info from prod and test I could help you better (DESC
prod;).



# Create the empty shell of test (all column definitions);

CREATE TABLE prod_new
SELECT * FROM test
WHERE 1=2;

# populate prod_new with prod data
INSERT INTO TABLE prod_new
SELECT * FROM prod;

- Verify the prod_new is correct
- DROP prod
- RENAME prod_new to prod.

- Scott










> Pardon the newbie question but is there a tool available for MySQL that I
> can give it 2 databases and it give me the ALTER TABLE statements
necessary
> to make 2 identical to 1?
>
> Specifically, I have a test and prod database.  I've made structural
changes
> to test and want to propagate them to prod.  Is there an easy way to do
> this?
>
> TIA,
> Cal
>
> Cal
> http://www.calevans.com
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
>
--
--
   Scott A. Gerhardt  P.Geo.
   Gerhardt Information Technologies
   [EMAIL PROTECTED]
--



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Database structure DIFF

[scott]

I'm not sure what you mean by "structural changes".

If the the two table have the same number of columns and they are in the same order, 
you can do something like what I have explained below.  It you included the table info 
from prod and test I could help you better (DESC prod;).



# Create the empty shell of test (all column definitions);

CREATE TABLE prod_new
SELECT * FROM test
WHERE 1=2;

# populate prod_new with prod data
INSERT INTO TABLE prod_new
SELECT * FROM prod;

- Verify the prod_new is correct
- DROP prod
- RENAME prod_new to prod.

- Scott










> Pardon the newbie question but is there a tool available for MySQL that I
> can give it 2 databases and it give me the ALTER TABLE statements necessary
> to make 2 identical to 1?
> 
> Specifically, I have a test and prod database.  I've made structural changes
> to test and want to propagate them to prod.  Is there an easy way to do
> this?
> 
> TIA,
> Cal
> 
> Cal
> http://www.calevans.com
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
>
--
--
   Scott A. Gerhardt  P.Geo.
   Gerhardt Information Technologies
   [EMAIL PROTECTED]
--


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php