Re: Database design question
I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. One table, with a user_id field. If you're worried about searching through millions of records, perhaps you could have archival tables that don't normally get searched and move messages from one to other after they get old... -p -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design question
One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- 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 design question
Thanks everyone. Now I feel confident that one table will be fine (Tripp's stat of 30 million records put me at ease :) ). Cheers, -James On Aug 7, 2006, at 4:08 PM, John Meyer wrote: One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
Wow, I didn't know that can happen. I'll definitely take that into consideration. Thanks Brent. On Aug 7, 2006, at 4:26 PM, Brent Baisley wrote: If you're looking to be put at ease, I've got a table with 250+ million records, but I've heard of people with larger tables than that on this list. You might want to also looking into using a compound primary key, meaning userid+messageid. Something like this: CREATE TABLE `message` ( `userid` int unsigned NOT NULL default '', `messageid` int unsigned NOT NULL auto_increment, `message` text, ... PRIMARY KEY (`userid`,`messageid`) ) What that does is give each user their own incrementing message id. Then you can do things like allow users to enter a message id directly with a number that would be easy for them to remember. Just an idea. - Original Message - From: James Tu [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, August 07, 2006 4:11 PM Subject: Re: Database design question Thanks everyone. Now I feel confident that one table will be fine (Tripp's stat of 30 million records put me at ease :) ). Cheers, -James On Aug 7, 2006, at 4:08 PM, John Meyer wrote: One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
On 8/7/06, James Tu [EMAIL PROTECTED] wrote: If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? Hi James, There are really two elements to this problem. The first element is how quickly MySQL can extract the messages you want from a large table. This requires that you know in advance the type of queries you're going to do (all messages by one user? all messages in a certain time window?) and be sure that these queries are approximately O(log N) rather than O(N) or worse. You will need to change your database design to fit the queries that you'll be doing. O(log N) queries would generally be characterized by the fields you're searching or sorting on being key fields (i.e. MySQL makes an index or BTREE or whatever it makes rather than having to go through the entire table linearly). The second element is data presentation. In developing web applications at least, if the first data on a page is displayed by the browser while the rest of the data is loading, the user perceives the load as being faster than it really is because the user is looking at the first data while the rest is loading. So, to make things more snappy, you might do more than one query to avoid large result sets. Dave.
Re: database design question
james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: database design question
I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
Re: database design question
If you posted your actual table structures (SHOW CREATE TABLE xx\G) I think I could be more helpful. Right now I am just shooting in the dark. Shawn Green Database Administrator Unimin Corporation - Spruce Pine James [EMAIL PROTECTED] wrote on 04/26/2005 02:15:49 PM: I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
Re: database design question
I haven't created real project tables yet. But here are the test ones that I'm experimenting with. CREATE TABLE east ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, east_1 varchar(255) default NULL, PRIMARY KEY (id) ) ; CREATE TABLE north ( north_id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, north_1 varchar(255) default NULL, north_2 varchar(255) default NULL, north_3 varchar(255) default NULL, PRIMARY KEY (north_id) ) ; CREATE TABLE south ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, south_1 varchar(255) default NULL, south_2 varchar(255) default NULL, south_3 varchar(255) default NULL, timestamp timestamp(14) NOT NULL, PRIMARY KEY (id) ) ; CREATE TABLE west ( west_id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, west_1 varchar(255) default NULL, PRIMARY KEY (west_id) ); I want to search on the keywords in all of these tables and retrieve the records from each table that fits the WHERE clause. The question is...should I just: (1) Make four queries and programmatically keep track of the results from each table? ...or (2) Create another table (let's call it `keywords`) and pull out the keywords into this new table...and store an ID that exists in north,south, east, west...and also store a column that tells us which table this ID is from?...Then we do a query on this table? I guess either way I would have to programmatically at some point fetch with four queries... At 2:46 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: If you posted your actual table structures (SHOW CREATE TABLE xx\G) I think I could be more helpful. Right now I am just shooting in the dark. Shawn Green Database Administrator Unimin Corporation - Spruce Pine James [EMAIL PROTECTED] wrote on 04/26/2005 02:15:49 PM: I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) --- -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
Re: Database design question
Mahmoud, Are these values atomical? My other question is what are the repercussions of not putting a table in 2nd and 3rd Normal Form. Your 'choice1-subchoice1' etc are combined values, so they aren't atomic. From your three example dropdown values, it looks as if 'choice' and 'subchoice' have a many-to-many relationship, and if that's so, you'll probably need a second table for 'subchoice' and a third table to store combinations of choice and subschoice. There are examples of disadvantages of not putting a table in 2NF or 3NF at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf, click on 'Normalisation and the normal forms', read the sections on 1NF, 2NF and 3NF. PB - Mahmoud Badreddine wrote: Hello, I have two questions: I would like to know whether I am violating the principle of atomicity in doing the following: I have a form which has a field with the following options: Choice1, choice2 and choice3. Each of the above choices have two further subchoices : subChoice1, subchoice2. so in my form I have a pull-down list and these are the choices I have: choice1-subchoice1 choice1-subchoice2 choice2-subchoice1 and so on. Are these values atomical? My other question is what are the repercussions of not putting a table in 2nd and 3rd Normal Form. Thank you. No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 4/14/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 4/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
JOHN MEYER [EMAIL PROTECTED] wrote on 07/04/2004 15:39:10: Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. You need to provide more information. For example, do prices differ with different wax types? Are you expecting some sort of validation that candle type A can be manufactured in wax type B? The easiest answer is just to have an extra column waxtype and have the ordering application only allow valid settings to be put in it, allowing NULL in the case where there is no choice of waxes. You seem to have discarded this option - but you haven't told us why, so we cannot tell what alternative to suggest. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
Everything I've read about creating online stores is that you are selling inventory items, not the items that makeup the inventory item. So if you sell a red candle made from wax X, candle is the product and red wax X are two attributes of the product. Ideally your structure would work for any product, which means no columns like waxtype or color. Off the top of my head I'm thinking a product table with things like name, description, price, etc. An attribute table. And a productattribute table to link the two. Your attribute table could be a multipurpose table from which you could group your attributes into categories (i.e. wax type, colors, etc.). Something like this: IDPrime IDCategory AttrName AttrDesc The IDCategory field would be a relation to the IDPrime field (a self join). If the IDCategory field is '0', you know it's a top level category. This gives you the ability to create a hierarchy with unlimited depth. The data might look like this: 1 0 Candles Burns bright for hours 2 1 Shape Shapes of candles 3 2 Round 4 2 Square 5 2 Pyramid 6 1 Color Colors available 7 6 Red 8 6 Blue 9 6 Gold 10 1 Wax Type 11 10 X 12 10 Y 13 10 Z You can add as many attributes as you like without having to ever change your database structure. So if they are buying a Candle, you search the attributes for Candles and get Shape, Color and Wax type (scent, logo, etc). The user then picks each attribute. If you code it right, you wouldn't even have to change you code when you add attributes and categories. You then store the user selection in the productattribute table. The Product table would be pre-populated with the products you offer, including their links to the attributes. But the design is flexible enough to allow a user to create a custom product or customize and existing one. The shopping cart then only contains the link to the product table. Obviously there is a lot more too creating the whole system, but hopefully this gives you some ideas. On Apr 7, 2004, at 10:39 AM, JOHN MEYER wrote: Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. -- 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 design question
I'm not 100% sure as to what you are trying to do, however, the relationship you describe could, I believe, be modeled as: Candles (candleid, description, price) Waxes (waxid, name/description) Candle_Waxes (candleid, waxid) Thus one table holds the description and price of each candle, another table holds the name of each wax, and a third table connects the two - as a candle can have multiple waxes, the logical way to do this (to me, anyway) is via this third table - glueing the other two together. You'll need to be a bit clever when querying, as simplying joining all three together will bring back multiple rows for candles which contain more than one wax - this could be eliminated by not bringing back the wax details (and using distinct), or in a number of other ways. One other way might be to come up with a way to combine all of the wax names into one field (tricky - can't think how to do this in mysql, off the top of my head). What precisely are you trying to achieve, though - this might be completely wrong for you! Thanks, Matt -Original Message- From: JOHN MEYER [mailto:[EMAIL PROTECTED] Sent: 07 April 2004 15:39 To: [EMAIL PROTECTED] Subject: Database design question Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Design Question...
[snip] Let's say that I have users Mary, Joe, Frank, and Dan. I also have servers panther, cheetah, jaguar and lion. The data for each account that I want to maintain is UID, GID, home directory, and default shell. In designing a table or tables to handle this example what can I make as a primary key? My idea was to have a table named mary, with a row With the name mary, are you implying a separate table for each user? If so, that doesn't sound right. I think you should have three tables: (1) A table of users, with properties that depend on the user but not those that might vary with server; (2) A table of servers, with properties that are indepedent of users; (3) A table with primary key formed by a pair of foreign keys, one pointing at the user table, one at the server table. This table would have properties that depend on the user/server combination. (An example would be the shell, since presumably a user could have different shells on different servers.) for each server, and each column would hold the data such as UID, GID, etc. This would mean that the primary key for each row would simply be the server name. By holding all of the data, including server name, in a single table, I am not sure how I would define a primary key. I couldn't use the user name or server name as there would be duplication. I suppose I could use a dummy numeric field that is auto-incrementing, but I am not sure how good an idea this is. I think I have read somewhere that you can actually use a combination of multiple columns as a primary key or index, but this is something I am obviously not familiar with. One other concern I have is regarding performance. The database work I have done so far has been dealing with relatively miniscule amounts of data. This database table however is going to contain information for about 80 servers with somewhere around 300 users per server on average. This is quite a large number of rows from my very limited experience. I Nah, doesn't sound like much data to me. don't want to come up with a poor table design that ends up causing problems down the line. [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Design Question...
On 18-Jun-2003 NIPP, SCOTT V (SBCSI) wrote: Hello... I am currently working on a User Account Management system. I am actually a Unix SA who is moonlighting at work as a MySQL DBA and web developer. I am learning a lot and enjoying the work, but I am severely lacking in database design fundamentals. I have created a couple very simple databases, but my two newest projects are getting more sophisticated. I was hoping for some DB design help with the following example... Let's say that I have users Mary, Joe, Frank, and Dan. I also have servers panther, cheetah, jaguar and lion. The data for each account that I want to maintain is UID, GID, home directory, and default shell. In designing a table or tables to handle this example what can I make as a primary key? My idea was to have a table named mary, with a row for each server, and each column would hold the data such as UID, GID, etc. This would mean that the primary key for each row would simply be the server name. By holding all of the data, including server name, in a single table, I am not sure how I would define a primary key. I couldn't use the user name or server name as there would be duplication. I suppose I could use a dummy numeric field that is auto-incrementing, but I am not sure how good an idea this is. I think I have read somewhere that you can actually use a combination of multiple columns as a primary key or index, but this is something I am obviously not familiar with. One other concern I have is regarding performance. The database work I have done so far has been dealing with relatively miniscule amounts of data. This database table however is going to contain information for about 80 servers with somewhere around 300 users per server on average. This is quite a large number of rows from my very limited experience. I don't want to come up with a poor table design that ends up causing problems down the line. Well, that's about all I can think of at the moment. I am sure that I will have plenty more questions as this progresses. Thanks again for the feedback. 300 * 80 =24,000 rows --this is _NOT_ a lot. At first, I'd use just one table: srvr varchar(32) NOT NULL, login varchar(32) NOT NULL, uid smallint unsigned NOT NULL DEFAULT 1000, gid smallint unsigned NOT NULL DEFAULT 1000, gecos varchar(128), sh varchar(32) NOT NULL DEFAULT '/bin/sh', home varchar(64), ... more fields ... primary key (login,srvr)) A next refinement would change the 'srvr' field to: srvr tinyint unsigned and create a 'server' table: id tinyint unsigned AUTO_INCREMENT, // last octet of ip ? name varchar(64) NOT NULL, primary key (id)) That'll make it easy to rename servers. The table(s) could be populated very easily with a couple of shell, Perl, and/or awk scripts. YP/NIS would come in handy as well. You'd loop on each distinct server name --request a ypxfer of the passwd map, then suck it into your table. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Design Question
On 3/28/02 1:39 PM [EMAIL PROTECTED] wrote: What I want to do is have a database that keeps track of large distribution lists. Each list has a unique ID, an owner (which is a reference to an ID in another table) and a creation date. My question is this: Would it be more efficient to have each entry in the list stored in this table in the database, or would I be better off having a reference to a file that is stored on the hard disk? Users must be able to view, add, modify and delete entire lists and single entries in the lists. I would recommend: A table of lists (one row for each list with owner, ID and creation Date (add modification date?)) A table of list entries (one row for each entry) A third table linking the list to list entry. You may have to parse the lists at first to separate the list entries but this method maintains a lot of flexibility: To delete an entire list remove the row from the lists table. This would allow you to maintain the list entries separately or you could also use a DELETE FROM linkingTable WHERE listID=$whatever. Deleting individual records would involve a single row in the list entry table and a DELETE FROM linkingTable WHERE listEntryID=$whatever. You probably would not have to sacrifice speed for flexibility: in small tables (200 records with an index) this type of operation takes less than .001 of a second on my (shared) server. Hope this helps! Chris -- Chris Adams Cypress Lake Studios Hypermedia, Quicktime, and Internet Design http://www.cypresslakestudios.com [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 Design Question
-Original Message- From: Ben Holness [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] ... My question is this: Would it be more efficient to have each entry in the list stored in this table in the database, or would I be better off having a reference to a file that is stored on the hard disk? If you mean efficient in terms of performance, the answer depends on how big the data is that would go in the files v. a database record, and how often they'll be accessed. If you mean more efficient in terms of disk space, there's probably little difference if the files are large, but a big difference if there are lots of little ones. How much difference for the latter depends on what file system you're using. In any event, you'll probably have to experiment. If you're talking about a lot of relatively small files, putting the data into database fields will probably win. Nick - 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 Design Question
Hi Ben: Performance would definitely be better if you store the data in the database as opposed to simply storing references to files. Sounds like you could solve this with 2 tables with one for the actual lists (assuming the structure of all your list is the same) and the other for the list header info (like owner and creation date). Craig At 07:39 AM 3/28/2002, you wrote: Hi all, I am in the process of designing a MySQL database that will run on Redhat 7.1, but I am fairly sketchy on MySQL and performance. What I want to do is have a database that keeps track of large distribution lists. Each list has a unique ID, an owner (which is a reference to an ID in another table) and a creation date. My question is this: Would it be more efficient to have each entry in the list stored in this table in the database, or would I be better off having a reference to a file that is stored on the hard disk? Users must be able to view, add, modify and delete entire lists and single entries in the lists. I am using MySQL, PHP and Perl with an Apache Webserver. Cheers, Ben - 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 Design Question
Hi Chris/Nick/Scalper, Thanks for the replies. I am not too sure how to implement this in tables, so I will give an example: Let's say I have three lists - a,b and c. List a contains 10,000 entries, list b contains 2,500 entries and list c contains 75,000 entries. I have a table of lists, with list ID, owner and date fields: a,Ben,28/03/2002 b,Ben,28/03/2002 c,Tom,28/03/2002 Then I have another table for each list, with all of the entries. The fields would be list ID and entry: a,entry1 a,entry2 a,entry3 ... b,entry1 b,entry2 ... c,entry1 c,entry2 c,entry3 ... Have I understood the two table concept correctly? How does the third table fit in? I guess that if two (or more) of the entries overlap, I could make things even better by having a multi-value field for the list ID in the second table, allowing: bc,entry65 if entry65 was common to lists b and c ... Is there a limit to the number of values in a multi-value field? (Does MySQL offer multivalue fields?) and would this be appropriate? Cheers, Ben I would recommend: A table of lists (one row for each list with owner, ID and creation Date (add modification date?)) A table of list entries (one row for each entry) A third table linking the list to list entry. - 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 Design Question
In your situation (or any situation, IMHO), multi-value fields defeat the purpose of good database design. I think you are on the right track if you are willing to tolerate a little redundancy for the sake of simplicity (as a one-to-many relationship). If you really want to structure it as a many-to-many relationship, then try the following (I throw my 2 cents without knowing exactly what you are dealing with in terms of data structures): Table 1: list (entity table) listId, owner, date, ... Table 2: entry (entity table) entryId, entryDetails (attribute 1, 2, 3, ...)... Note: Lump all entries from different lists together and use an auto_increment primary key and a unique key constraint for every entry. Table 3: listEntry (relation table) listId, entryId Such as: A, entry10 B, entry20 B, entry65 C, entry65 D, entry50 Note: Make (listId, entryId) a primary key or at least a unique key. Hope this helps. Jack --- Ben Holness [EMAIL PROTECTED] wrote: Hi Chris/Nick/Scalper, Thanks for the replies. I am not too sure how to implement this in tables, so I will give an example: Let's say I have three lists - a,b and c. List a contains 10,000 entries, list b contains 2,500 entries and list c contains 75,000 entries. I have a table of lists, with list ID, owner and date fields: a,Ben,28/03/2002 b,Ben,28/03/2002 c,Tom,28/03/2002 Then I have another table for each list, with all of the entries. The fields would be list ID and entry: a,entry1 a,entry2 a,entry3 ... b,entry1 b,entry2 ... c,entry1 c,entry2 c,entry3 ... Have I understood the two table concept correctly? How does the third table fit in? I guess that if two (or more) of the entries overlap, I could make things even better by having a multi-value field for the list ID in the second table, allowing: bc,entry65 if entry65 was common to lists b and c ... Is there a limit to the number of values in a multi-value field? (Does MySQL offer multivalue fields?) and would this be appropriate? Cheers, Ben I would recommend: A table of lists (one row for each list with owner, ID and creation Date (add modification date?)) A table of list entries (one row for each entry) A third table linking the list to list entry. - 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! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Database Design Question
Nick, This is pretty familiar to me because I'm analyzing the behavior of people in on-line discussions, so I'm gathering such data. Which begs the questions: - in what way are you analyzing behavior? and - in what way are you analyzing this list-community? =dn MySQL list busting - 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 Design Question
-Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] ... Which begs the questions: - in what way are you analyzing behavior? and - in what way are you analyzing this list-community? There's too much to read, is the simple answer to the first question. Over the last few years, I've developed some methods of identifying the most influential people in on-line discussions by discovering social networks. Among them, I'm quite sure, are people who are opinion leaders. By identifying them, it's possible to track ideas and trends. Not long after I first started doing this, I did some brainstorming about it with O'Reilly Associates, which uses such techniques to help decide which open source projects have momentum, so that they can make better decisions about what books to publish. In fact, MySQL v. mSQL was one of the early areas they studied. I also started a company, Opion Inc., to do this kind of analysis for the stock market, movies and other areas. Opion recently sold its technology to Intelliseek (and I have no idea what they're doing with it, but it's not hard to imagine). They seem to have a server on-line at www.opion.com, but it's secured. As for this community, my analysis is limited to reading the messages that either have interesting subjects or senders. By eyeballing the folder that contains them, that is, since my software tries to figure out what's interesting as well. Just to put some fears to rest, which usually come up quickly, I long ago recognized that the list of influential people is something of a spammer's dream. Equally long ago, it was obvious that such lists have to be unavailable to anyone else. It's an interesting, familiar paradox -- how to use the data without revealing enough that it could be abused. I don't want to get any further into the various issues around this research here unless it has something to do directly with MySQL. Nick - 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 Design Question
Thanks very much to every who helped me with my MySQL problem! I will probably go with the three table solution as it also eliminates the need for yet another table! Cheers, Ben - 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 design question
On Saturday 28 July 2001 15:09, Scott Goldstein wrote: I'm new to MySQL and database design and I have a questions concerning entities with common attributes. Suppose I have two entities, foo and bar with the following attributes: foo: (id, A, B, C, D, F) bar: (id, A, B, C, X, Y) Well, I just played with it here, using an addressbook and Zipcode tables and it seemed to work, should work for you too:-) select * from foo,bar where foo.id=bar.id; You would put 'A,B,C, in one of the tables then based on the id's of the two tables, all the fields would then be displayed:-) Both entities have several attributes in common. Is there any way to take advantage of this? I recall reading the MySQL does not support table inheritance. Is there any other mechanism that I can use? Thanks for the help. Scott - 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
