Database Structure Opinions Please
I am at the planning stage of building a database that will have a fairly large amount of different information. It is a DB for law firms, so it will have all of their contact information including a county. Also an individual lawyer will be listed. Add to that 2 description areas (one for firm, one for lawyer), then there will be individual check boxes for areas of practice, I am thinking about 10. There will also be a need to store articles that will be written by these attorneys. I am thinking one main table with the firm info, a separate table for atty info, third for areas of practice and fourth for articles? I would enjoy hearing opinions on this. -- Gary __ Information from ESET Smart Security, version of virus signature database 5868 (20110212) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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]
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]
database structure (fwd)
is this list working? my last post failed? re-try. T. Hiep -- Forwarded message -- Date: Mon, 2 Jul 2007 05:53:17 -0700 (PDT) From: Hiep Nguyen [EMAIL PROTECTED] 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
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: Arbitrary Boolean Functions as Relational Database Structure?
Hi David, David T. Ashley wrote: Hi, I'm implementing a software release database. Users may aribtrarily be members of groups (a many-to-many mapping), and each software release may contain multiple files. I'd like to allow users the maximum flexibility in deciding who may view what software releases. The most obvious approach is to allow specification in the form of Release X may be viewed by Users in Group Y or Group Z, per release. In this case, the database design would be something like: [Users] (many:many) [Groups] (many:many) [SwReleases] (1:many) [Files] The many:many relationship between groups and software releases specifies a Boolean function, of the form is in Group X or is in Group Y or Since one knows the user who is logged in (for a web database), one can do an outer join and quickly find all the software releases that the user may view. I believe one can do it in O(log N) time. However, the Boolean function is of a fairly special form (is in Group X or is in Group Y ...). This is the only form where it seems to translate to an SQL query naturally. Here is my question: Is there any interesting way to structure a database so that other forms of permissions can be specified and translate directly into SQL queries? For example, what if, for a software release, one says, to view this software release, a user must be in Group X or Group Y, but not in Group Z and not user Q? Is there a database structure and a corresponding O(log N) query that will quickly find for a given user what software releases may be viewed? I have developed such a system over the past 7 years or so. It is quite complex to explain, but it's really simple when you get down to it. I wrote a two-part series about it on my blog: http://www.xaprb.com/blog/2006/08/16/how-to-build-role-based-access-control-in-sql/ http://www.xaprb.com/blog/2006/08/18/role-based-access-control-in-sql-part-2/ From your description of the problem, I would say a subset of my solution could fit your needs exactly, and be about as simple and efficient as I believe is possible. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Arbitrary Boolean Functions as Relational Database Structure?
This seems like a simple query to me. Correct me if I am wrong but as I understand it you want to get a list of SwReleases that a user has access to. Would something like this not work SELECT s.ID, s.Name FROM SwRelease as s WHERE s.ID IN ( SELECT SwID FROM GroupSwRel-- this table is the group to SwRelease relation ship WHERE GroupID IN ( SELECT GroupID FROM UserGroupRel -- this table is the user to group relationship. WHERE UserID = 'someuserid')) I've never done a query with a sub query in a sub query but it seems like it should work to me. In fact, if I'm not mistaken you may be able to rewrite this just using joins. Chris W David T. Ashley wrote: Hi, I'm implementing a software release database. Users may aribtrarily be members of groups (a many-to-many mapping), and each software release may contain multiple files. I'd like to allow users the maximum flexibility in deciding who may view what software releases. The most obvious approach is to allow specification in the form of Release X may be viewed by Users in Group Y or Group Z, per release. In this case, the database design would be something like: [Users] (many:many) [Groups] (many:many) [SwReleases] (1:many) [Files] The many:many relationship between groups and software releases specifies a Boolean function, of the form is in Group X or is in Group Y or Since one knows the user who is logged in (for a web database), one can do an outer join and quickly find all the software releases that the user may view. I believe one can do it in O(log N) time. However, the Boolean function is of a fairly special form (is in Group X or is in Group Y ...). This is the only form where it seems to translate to an SQL query naturally. Here is my question: Is there any interesting way to structure a database so that other forms of permissions can be specified and translate directly into SQL queries? For example, what if, for a software release, one says, to view this software release, a user must be in Group X or Group Y, but not in Group Z and not user Q? Is there a database structure and a corresponding O(log N) query that will quickly find for a given user what software releases may be viewed? Thanks. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Arbitrary Boolean Functions as Relational Database Structure?
Hi, I'm implementing a software release database. Users may aribtrarily be members of groups (a many-to-many mapping), and each software release may contain multiple files. I'd like to allow users the maximum flexibility in deciding who may view what software releases. The most obvious approach is to allow specification in the form of Release X may be viewed by Users in Group Y or Group Z, per release. In this case, the database design would be something like: [Users] (many:many) [Groups] (many:many) [SwReleases] (1:many) [Files] The many:many relationship between groups and software releases specifies a Boolean function, of the form is in Group X or is in Group Y or Since one knows the user who is logged in (for a web database), one can do an outer join and quickly find all the software releases that the user may view. I believe one can do it in O(log N) time. However, the Boolean function is of a fairly special form (is in Group X or is in Group Y ...). This is the only form where it seems to translate to an SQL query naturally. Here is my question: Is there any interesting way to structure a database so that other forms of permissions can be specified and translate directly into SQL queries? For example, what if, for a software release, one says, to view this software release, a user must be in Group X or Group Y, but not in Group Z and not user Q? Is there a database structure and a corresponding O(log N) query that will quickly find for a given user what software releases may be viewed? Thanks.
Suggestions needed for designing a database structure
Hello all, I am currently working on redesigning a database structure for online translation utility called Entrans [1]. Entrans is quite nice piece of software, but unfortunately it has one major design flaw: it only supports languages which have only two plural forms and to fix this issue, a database structure has to be redesigned. The way Entrans works is simple: it parses the PO-catalogs [2] and inserts the entries found in the file into the database. The main issue is how do I store these entries, because there are two different types: a) a singular entry b) a plural entry With singular entries it's easy, we following items of data: flags comments msgid msgstr Now, the problem is storing the plural entries, which have four static items, followed by the n 1 of dynamic entries: flags comments msgid msgid_plural msgstr[0] ... msgstr[N] At first, I tried to do it like this: msgid_table: id msgid msgcontext_table: id msgid_id (FK for msgid_table.id) is_fuzzy (flag) comments file_id (FK for file_table.id, not presented here...) msgid_plural (present if msgid_plural exists) plural_count (number of plural forms) msgstr_table: id msgcontext_id (FK for msgcontext_table.id) user_suggestion index (to which of these msgstr[n] fields it will be put, 0 if string doesn't have any plural forms) is_valid (is it valid translation suggestion) Now, as you can see, this approach doesn't work at all... because it's quite a hell designing a query based on msgid_table.id - msgcontext_table.mgsid_id - msgcontext_table.id - msgstr_table.msgcontext_id. What I'm thinking is that the msgcontext_table.id should be made a superkey, but this does not help when I need to figure out which of the suggestions are valid for current msgid. Your ideas are very welcome ;) [1] http://entrans.sourceforge.net/ [2] http://www.gnu.org/software/gettext/manual/html_node/gettext_9.html Best regards, Priit -- 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]
database structure question...
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: 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]
Database structure
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]
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]
[mysql] replication of database structure changes
Hi List, Do changes in database structure replicate to the slaves from the master? Is there a document somewhere in the manual (I have not found one) that explains what gets replicated and what does not? Specifically, does an ALTER TABLE get replicated? There is some mention that replication is for data and not for structure, but a formal explanation of what exactly that means is hard to find. Thanks again. Jim N. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql] replication of database structure changes
In the last episode (May 04), Jim said: Do changes in database structure replicate to the slaves from the master? Is there a document somewhere in the manual (I have not found one) that explains what gets replicated and what does not? Specifically, does an ALTER TABLE get replicated? There is some mention that replication is for data and not for structure, but a formal explanation of what exactly that means is hard to find. All commands that modify data, including CREATE|DROP TABLE|DATABASE, replicated. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database structure
Hello, 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]
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]
On database structure -- keys and speed
Hi, all, I'm still very much a newbie at MySQL, and thought a good starter project would be a simple places database of every populated place-name in the world along with its latitude and longitude. I already have the data, and originally planned to have only 4 tables with 2-4 fields each: Places: placename, adm2code, lat, lon ADM2 (e.g. county): adm2name, adm2code, adm1code ADM1 (e.g. state): adm1name, adm1code, countrycode Country: countryname, countrycode After importing the data, however, I realized that my key-ing must be wrong. I didn't key the placename because they're not guaranteed to be unique, but that means that a search for Artesia takes many (~30) seconds to complete, which is really not very useful. Does anyone have any suggestions on how this is done? I'm sure the same problem comes up when searching for Tom Jones in a names database... Thanks in advance. - Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: On database structure -- keys and speed
I didn't key the placename because they're not guaranteed to be unique, but that means that a search for Artesia takes many (~30) seconds to complete, which is really not very useful. You should not use the placename as a PRIMARY KEY, but it can still have an index. You really should add a primary key to the placename table, though. To start I'd recomend the following: ALTER TABLE places ADD INDEX place_index (placename); ALTER TABLE places ADD COLUMN id int unsigned auto_increment primary key; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Export Database Structure sans data
Im looknig for the function that will allow me to export the database structure into a static file, without the actual data. I would like to create an ERD diagram with the output file. David
Re: Export Database Structure sans data
David Perron wrote: Im looknig for the function that will allow me to export the database structure into a static file, without the actual data. I would like to create an ERD diagram with the output file. David mysqldump -d -- 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: Export Database Structure sans data
phpMyAdmin also give a nice, simple frontend for doing this... copy tables or db's with/without data. Personally, I don't think mysql should be used on a box without phpMyAdmin on it, at least as a backup admin tool;) -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 26, 2004, at 5:05 PM, Daniel Kasak wrote: David Perron wrote: Im looknig for the function that will allow me to export the database structure into a static file, without the actual data. I would like to create an ERD diagram with the output file. David mysqldump -d -- 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]
Where to find advice on database structure/design?
I have moved our database from Access to MySQL, but I did leave same structure. Problem is that in our business we deal with companies that have multiple branches so having it like we have now with sub queries and subforms that have to look up info from 3 tables to give me one record of contact information just does not work anymore. Too many forms with subforms create huge performance problems. Anyone can point me to a good reading material (on the web or in print) that would give me ideas how to deal with this overcomplicated way of handling client data? Thanx, Apolinaras Apollo Sinkevicius Carmel Music Entertainment, LLC web-site: http://carmelme.com Having an event in Chicago, or would you like to bring Chicago entertainment to your event? Give Carmel Music Entertainment a call for the finest entertainment available in Chicago. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where to find advice on database structure/design?
Read up on database normalization. (do a search on your favorite search engine) It will give you an appreciation of storing related pieces of info in different tables. Though you shouldn't need a series of forms to access the data (most of the time) - this has more to do with your programming logic. With a strong understanding of normalization you'll be able to structure your forms to get the info you want. Here's a great site with examples of different data models. Not all are complete, but a great start and a great source for ideas. http://www.databaseanswers.com/ olinux --- Apollo (Carmel Entertainment) [EMAIL PROTECTED] wrote: I have moved our database from Access to MySQL, but I did leave same structure. Problem is that in our business we deal with companies that have multiple branches so having it like we have now with sub queries and subforms that have to look up info from 3 tables to give me one record of contact information just does not work anymore. Too many forms with subforms create huge performance problems. Anyone can point me to a good reading material (on the web or in print) that would give me ideas how to deal with this overcomplicated way of handling client data? Thanx, Apolinaras Apollo Sinkevicius Carmel Music Entertainment, LLC web-site: http://carmelme.com Having an event in Chicago, or would you like to bring Chicago entertainment to your event? Give Carmel Music Entertainment a call for the finest entertainment available in Chicago. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where to find advice on database structure/design?
It might also help to at least try to understand why you normalize. Don't just try to follow the rules, there is some art to it as well. My favorite book on this is Database Design for Mere Mortals Thanks, Eric At 09:11 PM 10/22/03 -0700, olinux wrote: Read up on database normalization. (do a search on your favorite search engine) It will give you an appreciation of storing related pieces of info in different tables. Though you shouldn't need a series of forms to access the data (most of the time) - this has more to do with your programming logic. With a strong understanding of normalization you'll be able to structure your forms to get the info you want. Here's a great site with examples of different data models. Not all are complete, but a great start and a great source for ideas. http://www.databaseanswers.com/ olinux --- Apollo (Carmel Entertainment) [EMAIL PROTECTED] wrote: I have moved our database from Access to MySQL, but I did leave same structure. Problem is that in our business we deal with companies that have multiple branches so having it like we have now with sub queries and subforms that have to look up info from 3 tables to give me one record of contact information just does not work anymore. Too many forms with subforms create huge performance problems. Anyone can point me to a good reading material (on the web or in print) that would give me ideas how to deal with this overcomplicated way of handling client data? Thanx, Apolinaras Apollo Sinkevicius Carmel Music Entertainment, LLC web-site: http://carmelme.com Having an event in Chicago, or would you like to bring Chicago entertainment to your event? Give Carmel Music Entertainment a call for the finest entertainment available in Chicago. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database Structure
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 - 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, 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
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
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
FW: Help with Database structure..
-Original Message- From: Bret Ewin [mailto:[EMAIL PROTECTED]]On Behalf Of Bret Ewin Sent: Monday, January 21, 2002 12:03 PM To: 'Russell King' Subject: RE: Help with Database structure.. You may also want to break the URL into pieces (protocol, domain, path, file) and index each piece. This would allow searches for URLs in the same domain without having to use LIKE, which cannot be indexed. Bret -Original Message- From: Russell King [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 19, 2002 8:01 PM To: [EMAIL PROTECTED] Cc: Noah Silverman Subject: Re: Help with Database structure.. We thought about having one massive file for all this data, BUT, we archive something like 100,000 articles a day. We store articles for 6 months, so this table would very quickly have 18,000,000 rows. Now, we need to select a group of articles based on source and date. We also need to select based on url. Shouldn't be a problem. You can put the whole thing in one table with indexes on the various columns you'll be selecting on. In my experience it's best to tokenise the source, date and url information in a seperate table, thus storing the filename, and three integers. 1st integer - Days since 1st Jan 1980 (for example). If you don't need to store the full datetime field, then don't bother, just store a bigint. 2nd integer - Contains a reference to the source. You can store the actual source name in another table, with this reference number against it. (indexed of course). 3rd integer - Same as #2, but contains a reference number for the url. The url being held elsewhere. This will keep the indexes you lookup on nice and small as they store 3 integer values, (make sure you use the correct type of integer, BIGINT probably). If you always lookup on all three values, an index on all three is best. To lookup articles based on url, you check the url_table for the correct reference number. Then run a query on the big_table looking for that reference number. If you have duplicate urls, only store them once in the url_table, as it's just wasteful otherwise. This also makes for a faster url lookup. I can only imagine how long it would take to search 18,000,000 rows for a specific url. Checking the larger table with 18M rows would typically come back in well under a second on a 200Mhz PC, although you need around 80M index cache. If all the urls are unique, this table will become pretty large, and you'll need a fair sized index on that too. I would imagine 300M extra would do the trick, if you only index the first 14 characters or so. These figures scale up pretty well, until you run out of memory, when it all gets a bit sluggish. If that's the case, you can store the different tables on seperate PCs to speed up retrieval. After all -- you aren't doing any JOINs. Where it gets interesting is when you get a request to search all of these documents a la dejanews. It's actually not too difficult to build a very fast word search algorithm, but you'll start to deal with proper BIG tables, with billions of rows... You also need to factor in how reliable you want the system to be, and what your acceptable down-time is BTW - Loading tables over 1M records can be a pain in the arse when you need to recover, and there's no easy way to make it quicker. Russ. - 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
Help with Database structure..
Noah, Sunday, January 20, 2002, 1:55:00 AM, you wrote: NS The problem is that 20,000 tables can be a bit unwieldy for mysql to NS handle. I don't think it will be a sore work for MySQL to support more than 20,000 tables, but it's up to a inode limit on the filesystem on your OS. As Russel King correctly remarked, it's probably better to store all your 18M in one table. NS If you have any ideas, I would love to hear them. NS -N -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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
Help with Database structure..
I need some help with a database design question: To summarize our design (I'll spare you a lot of the details.) We collect text files from 20,000 different sources every day. The actual file is stored as a text file. Each source has its own table which contains a list of the text files, date, title, url, etc. We thought about having one massive file for all this data, BUT, we archive something like 100,000 articles a day. We store articles for 6 months, so this table would very quickly have 18,000,000 rows. Now, we need to select a group of articles based on source and date. We also need to select based on url. I can only imagine how long it would take to search 18,000,000 rows for a specific url. Our CURRENT concept is to have one table for each of the 20,000 sources. This way we can just query the matching table for what we need. The problem is that 20,000 tables can be a bit unwieldy for mysql to handle. If you have any ideas, I would love to hear them. Thanks, -N - 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: Help with Database structure..
We thought about having one massive file for all this data, BUT, we archive something like 100,000 articles a day. We store articles for 6 months, so this table would very quickly have 18,000,000 rows. Now, we need to select a group of articles based on source and date. We also need to select based on url. Shouldn't be a problem. You can put the whole thing in one table with indexes on the various columns you'll be selecting on. In my experience it's best to tokenise the source, date and url information in a seperate table, thus storing the filename, and three integers. 1st integer - Days since 1st Jan 1980 (for example). If you don't need to store the full datetime field, then don't bother, just store a bigint. 2nd integer - Contains a reference to the source. You can store the actual source name in another table, with this reference number against it. (indexed of course). 3rd integer - Same as #2, but contains a reference number for the url. The url being held elsewhere. This will keep the indexes you lookup on nice and small as they store 3 integer values, (make sure you use the correct type of integer, BIGINT probably). If you always lookup on all three values, an index on all three is best. To lookup articles based on url, you check the url_table for the correct reference number. Then run a query on the big_table looking for that reference number. If you have duplicate urls, only store them once in the url_table, as it's just wasteful otherwise. This also makes for a faster url lookup. I can only imagine how long it would take to search 18,000,000 rows for a specific url. Checking the larger table with 18M rows would typically come back in well under a second on a 200Mhz PC, although you need around 80M index cache. If all the urls are unique, this table will become pretty large, and you'll need a fair sized index on that too. I would imagine 300M extra would do the trick, if you only index the first 14 characters or so. These figures scale up pretty well, until you run out of memory, when it all gets a bit sluggish. If that's the case, you can store the different tables on seperate PCs to speed up retrieval. After all -- you aren't doing any JOINs. Where it gets interesting is when you get a request to search all of these documents a la dejanews. It's actually not too difficult to build a very fast word search algorithm, but you'll start to deal with proper BIG tables, with billions of rows... You also need to factor in how reliable you want the system to be, and what your acceptable down-time is BTW - Loading tables over 1M records can be a pain in the arse when you need to recover, and there's no easy way to make it quicker. Russ. - 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
synchronize database structure
Hi, I am looking for an easy way to synchronize two mysql-databases (only the sql-structure). I want to compare two structure dumps, find out the differences and get alter table statements as result. CU Dorthe - 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
Efficient Query/Database Structure?
Perhaps this is a common problem: I would like to have a database field that contains multiple code numbers and later search for the presence of one of the codes. What I have come up with so far is using a delimter between the code numbers to end up with a field like: 12534 When searching I'm using: REGEXP '^$search_category$|^$search_category|$search_category|$search_category$ ' This seems to work fine, however I was wondering if there is a better way... Any advice would be greatly appreciated. - anatole - 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: Efficient Query/Database Structure?
Create another table with an id column and a code column, and for each code
insert a column with the id of the row in the original database and the
code. Then do an SQL join to search. For example:
mysql create table example (id int(11) NOT NULL auto_increment, blah
varchar(100), PRIMARY KEY(id));
Query OK, 0 rows affected (0.02 sec)
mysql create table codes (id int(11) NOT NULL, code int(11) NOT NULL, KEY
code(code));
Query OK, 0 rows affected (0.02 sec)
mysql insert into example (blah) values ('hello');
Query OK, 1 row affected (0.01 sec)
mysql insert into codes values (last_insert_id(), 1);
Query OK, 1 row affected (0.00 sec)
mysql insert into codes values (last_insert_id(), 3);
Query OK, 1 row affected (0.00 sec)
mysql insert into example (blah) values ('hello world');
Query OK, 1 row affected (0.00 sec)
mysql insert into codes values (last_insert_id(), 2);
Query OK, 1 row affected (0.00 sec)
mysql insert into codes values (last_insert_id(), 4);
Query OK, 1 row affected (0.00 sec)
mysql select * from codes;
++--+
| id | code |
++--+
| 1 |1 |
| 1 |3 |
| 2 |2 |
| 2 |4 |
++--+
4 rows in set (0.00 sec)
mysql select * from example;
++-+
| id | blah|
++-+
| 1 | hello |
| 2 | hello world |
++-+
2 rows in set (0.00 sec)
mysql select example.* from example, codes where example.id = codes.id and
code
s.code = 3;
++---+
| id | blah |
++---+
| 1 | hello |
++---+
1 row in set (0.00 sec)
mysql select example.* from example, codes where example.id = codes.id and
code
s.code = 4;
++-+
| id | blah|
++-+
| 2 | hello world |
++-+
1 row in set (0.00 sec)
Perhaps this is a common problem:
I would like to have a database field that contains multiple code numbers
and later search for the presence of one of the codes. What I have come up
with so far is using a delimter between the code numbers to
end up with
a field like:
12534
When searching I'm using:
REGEXP
'^$search_category$|^$search_category|$search_category|$search
_category$
'
This seems to work fine, however I was wondering if there is a
better way...
Any advice would be greatly appreciated.
- anatole
-
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
building advice needed on database structure
I have a database project, historic building materials... I've designed 4 Tables like this (there are more fields which I didn't listed here): table products - primary key(productid) - product description ... table category - primary key(categoryid) - category description table subcategory - primary key (Subcatid) - subcatdescription table subtype - primary key (suptypeid) - subtype description now I like to join those tables. For example I got the product of a single door (there are other doors which are double doors. This product belongs to subtype single doors, which than belongs to the subcategory doors and category historic building material out of wood. My aim is to have a hyperlink which shows me the categorys if click on it it shows me the subcategorys and than the subtype and than the products which belong to all this. So if I now create another table called for example table Ids - productID - categoryID - subcatID - subtypeID will I be able to join those together and get all I want to achive building it like this? Or do I have to many tables to join together? Also the Id's make up my product code say for example category b.m.o of wood makes number 10 (20, 30, 40 for others) subcategoryid will be 1 for doors (2,3,4 there after) subtype also 1 (2,3,4) and than the products will be 1, 2 , 3 etc. My logic doesn't let me think of any other way. So if anyone has a good suggestion on how to go about this please let me know. Thank you so much if you find the time to help me out here. Regards Petra [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: Search engine database structure
Most search engines use databases. Some search directories or web addresses directly. Some use spiders to collect data. Search engines are as varied as the kinds of jobs they do. First, decide what needs to be searched and what searched for. Then research it. Don't just plug a stock program into a slot and expect it is going to do what you want. If you have ever explored college websites, you might notice that most are large affairs that often have a lot of disjointed and poorly organized pages. Most try to solve bad organization with a stock search engine like ht://Dig or Ultraseek, without realizing that for many prospective customers, it is a lame solution. The ideal, on the other hand, could be a lot more expensive and time-consuming to put together. In any case, take stock of your customers and what they are looking for, and do the research to find something that comes close. On 26 Jan 2001, at 2:26, Tobias Talltorp wrote: As I understand it, search engines use a "special" type of database structure. They have a special table for common words/phrases and another one with all the data. They link theese together by a third one that contains only the ids from the other two, thus making the search much faster. How do they determine the common words? Is it done similar to the FULLTEXT search in Mysql, all words over three characters? I think I have heard someone call it "many to many", but am not sure. Anyway, is there any good tutorial out there so I can design my own? Or perhaps someone could take a few minutes of their time and explain some of it to me. Thanks, // Tobias - 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 John Jensen 520 Goshawk Court Bakersfield, CA 93309 - 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
Search engine database structure
As I understand it, search engines use a "special" type of database structure. They have a special table for common words/phrases and another one with all the data. They link theese together by a third one that contains only the ids from the other two, thus making the search much faster. How do they determine the common words? Is it done similar to the FULLTEXT search in Mysql, all words over three characters? I think I have heard someone call it "many to many", but am not sure. Anyway, is there any good tutorial out there so I can design my own? Or perhaps someone could take a few minutes of their time and explain some of it to me. Thanks, // Tobias - 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
