RE: database structure
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
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
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
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
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
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
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
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
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 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... - 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...
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...
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...
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...
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...
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...
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...
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
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
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
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
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
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
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
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
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
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 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
RE: database structure script
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 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
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
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
