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]
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.
database design question
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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) ---
Database design question
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. -- -Mahmoud Badreddine
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]
A database design question
Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A database design question
I think you need to explain what kind of SELECTs you want to do, and what results you expect. How do you expect to get results from a SELECT which returns hits in both the B and C tables? If you expect to do this, then the D table is probably your correct answer. Do you really need a rec_type field? Can you not leave the columns which exist only in B type records null in c-type records and vice versa? How much commonality is there between B and C type fields? I presume there is some, or you would not be wanting to merge them. Incidentally, I think you only need a simple join, not a left join - unless I misunderstand. Alec Giulio [EMAIL PROTECTED] wrote on 11/10/2004 10:44:43: Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio -- 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: A database design question
Il giorno 11/ott/04, alle 11:53, [EMAIL PROTECTED] ha scritto: I think you need to explain what kind of SELECTs you want to do, and what results you expect. you're right, I'll try to explain it better I'm working on a system that must keep track of all the music broadcasted by a tv, so, let's call record A TVprogram, TVprogram table will contain all the programs broadcasted in cronological order, they have a broadcasting date, start time and end time, type of program ( news, entertainment, cartoons, and so on... ) every TVprogram record can have one or more associated records of two types: a record B ( let's call it MusicTrack ), containg info ( title, composer, etc. ) about a Music track used on some way in the TV program a record C ( let's call it Movie ), containing info ( title, director, etc. ) about a movie or serial or cartoon and so on broadcasted during the TV program. ( the list of music tracks used on the movie will be extracted from another database at later time). MusicTrack and Movie are associated to a given TVprogram on a progressive ( and cronological ) order. So I.E., for a TVprogram record I could have: 1 a MusicTrack record with info about a song used as intro for the program 2 a MusicTrack record with info about a song used as background music while talking about the movie that will be broadcasted 3 a Movie record containing info about the movie itself 4 a MusicTrack record with info about a song used at the end of the program the select I would like to perform is, given a TVprogram element, have a list of all its MusicTrack or Movie records in crological order, or have a list of TVprogram elements on a given interval, and for everyone of them a list of their referred records. but you're right, I now think the possible solutions are to merge the two table type on one table type, or keep them separated, perform two different separate joins and then merge them by code... thank you, Giulio How do you expect to get results from a SELECT which returns hits in both the B and C tables? If you expect to do this, then the D table is probably your correct answer. Do you really need a rec_type field? Can you not leave the columns which exist only in B type records null in c-type records and vice versa? How much commonality is there between B and C type fields? I presume there is some, or you would not be wanting to merge them. Incidentally, I think you only need a simple join, not a left join - unless I misunderstand. Alec Giulio [EMAIL PROTECTED] wrote on 11/10/2004 10:44:43: Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio -- 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] Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A database design question
My answers interspersed below (and yes, I have read his follow up reply that had additional information) Giulio [EMAIL PROTECTED] wrote on 10/11/2004 05:44:43 AM: Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. I would think that this structure (#2) would fit your model the best. Each Table A element could contain various elements of tables B and C depending on the schedule/format of that show. Table D would be your logging table that would relate elements of Table A to elements of Tables B or C along with a time stamp (based on what time in the program the element appeared.) I have seen many databases designed with a table like D that needed to contain references to objects of different types. I would create tableD to look something like this: CREATE TABLE content_log ( ID int auto_increment primary key, tableA_id int not null, Object_ID int not null, Object_type (here you have options, you could use a SET, ENUM, INT, or CHAR datatype), TimeOffset time not null ) 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio So.. if you wanted to find all of the movies shown during Show # 14 SELECT * FROM content_log WHERE tableA_ID = 14 and object_type='movie' /*or however you set up that column*/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Shawn Green Database Administrator Unimin Corporation - Spruce Pine
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.
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: Basic Database Design Question
Thanks guys. I wasn't very clear in my original email. I already had a business section table (with a primary key). My problem was, I couldn't figure out how to accurately store (for information retrieval purposes) information about business sections that use particular applications. I was orignally thinking about putting something like FMB/HR/OM, for example, as one character string in the 'used_by' column of the apps table. I now know that using multiple values as one string is NOT the way to go! The answer, as Jerimiah pointed out, if an INTERSECTION TABLE!! app_id, branch_id in one table, with app_branch_id being the primary key! Each row in this table will represent an app-branch use relationship scenario! Once I got this, it was like a huge lightbulb going off in my head! Wow! I have setup my intersection table and it works great. My intersection table, luckily, only has 21 rows. My next question is this: How in the world do you setup an intersection table (when designing the database) the use primary keys with thousands of rows? I could imagine an intersection table having thousands of scenario combinations. How do you tackle something like that? Thanks again guys! --- Jeremiah Jacks [EMAIL PROTECTED] wrote: You could do it many different ways. Here's one that I would consider: Create a table to store the business sections (buss_section) and a table to relate the apps with the business sections (app_buss_section). The design would abstract the app info from its usage and create a manageable table of business sections which could be easily modified. Something like this should work... Table nms_apps (..., id) Table buss_section (..., id) Table app_buss_section (nms_apps.id, buss_section.id) -Jeremiah -Original Message- From: James Walters [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 9:42 AM To: [EMAIL PROTECTED] Subject: Basic Database Design Question Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for our client. One column which I would like to use is called 'used_by', which would store information about which business sections (Financial Management Branch, Human Resources Branch, etc.) use a particular application. Often times more than one section use a particular application. My question is this: How do you handle multiple entries in one column? Is it acceptable to more than one value in a column for one row? If not, what is the best way to design that in the table? Have multiple 'used_by' columns? 'used_by_1', 'used_by_2', 'used_by_3', etc.? Thanks in advance, Testudo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Basic Database Design Question
Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for our client. One column which I would like to use is called 'used_by', which would store information about which business sections (Financial Management Branch, Human Resources Branch, etc.) use a particular application. Often times more than one section use a particular application. My question is this: How do you handle multiple entries in one column? Is it acceptable to more than one value in a column for one row? If not, what is the best way to design that in the table? Have multiple 'used_by' columns? 'used_by_1', 'used_by_2', 'used_by_3', etc.? Thanks in advance, Testudo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Basic Database Design Question
This is, as you guess, a very basic issue for relational databases. You never want to put multiple entries in one column, as you describe it. That's the scenario in which you create another table and use a key to join the two. For your project, you should have a column in the nms_apps table that uniquely identifies it -- the primary key, usually. You'll want to create a table, perhaps called 'user,' containing the 'used_by' information. That table would have, at a minimum, a column for the nms_apps key and a column that contains the user information. Then to find out who uses a given application, the query would be along these lines (this uses the column 'app_id' as the key: SELECT used_by FROM nms_apps, user WHERE nms_apps.app_id = user.app_id AND app_name = Application Foo Hope that helps. If you grasp this, you'll have the basic idea of relational data. Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -Original Message- From: James Walters [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 10:42 AM To: [EMAIL PROTECTED] Subject: Basic Database Design Question Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for our client. One column which I would like to use is called 'used_by', which would store information about which business sections (Financial Management Branch, Human Resources Branch, etc.) use a particular application. Often times more than one section use a particular application. My question is this: How do you handle multiple entries in one column? Is it acceptable to more than one value in a column for one row? If not, what is the best way to design that in the table? Have multiple 'used_by' columns? 'used_by_1', 'used_by_2', 'used_by_3', etc.? Thanks in advance, Testudo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- 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: Basic Database Design Question
You could do it many different ways. Here's one that I would consider: Create a table to store the business sections (buss_section) and a table to relate the apps with the business sections (app_buss_section). The design would abstract the app info from its usage and create a manageable table of business sections which could be easily modified. Something like this should work... Table nms_apps (..., id) Table buss_section (..., id) Table app_buss_section (nms_apps.id, buss_section.id) -Jeremiah -Original Message- From: James Walters [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 9:42 AM To: [EMAIL PROTECTED] Subject: Basic Database Design Question Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for our client. One column which I would like to use is called 'used_by', which would store information about which business sections (Financial Management Branch, Human Resources Branch, etc.) use a particular application. Often times more than one section use a particular application. My question is this: How do you handle multiple entries in one column? Is it acceptable to more than one value in a column for one row? If not, what is the best way to design that in the table? Have multiple 'used_by' columns? 'used_by_1', 'used_by_2', 'used_by_3', etc.? Thanks in advance, Testudo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- 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 Design Question...
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. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- 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]
Database design question
I am fairly new to this, so please bare with me on this. I am designing a database that stores information about the computers I manage, and am developing a PHP front end to add, view, and edit information about the computers. One issue I ran into is designing the hard drive information. Some computers have one hard drive, some have 2, 4, 16, etc. I am wanting to learn how to deal with this the correct way, so I want to create a separate table that stores the information about the drives, and have a foreign key that specifies the computer that owns the drive. I posted a message on the PHP-DB list, and received an answer that seems confusing. It told me that I needed InnoDB to be able to deal with this issue, but in the MySql documentation is says this:InnoDB provides MySQL with a transaction-safe (ACID compliant) table handler with commit, rollback, and crash recovery capabilities. Now having transactions available would be nice, but that is not my problem now. Now in another explaination on how to deal with relationships in MySql and with PHP it shows that you use PHP to deal with the relationships. I always thought that you let the database deal with the relationships, and not the application you are writing. Which is correct? I would think that if I created a relationship between tables, and I called for information on the computer, I would automatically get all of the drive information. Am I correct in this? Here is an example of the tables I have: ComputerTable computer_id - primary key computer_name etc. HardDriveTable drive_id - primary key computer_id - foreign key drive-capacity etc. If it is better to use the application code, I can do that, but I want to do things the right way, not necessarily the easiest. Mike - 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 Design Question
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
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
Database Design Question
Hello,
I have a table design question relating to best practice and performance.
Monthy is somewhat of a SQL guru and so may be many others on this list,
this may actually help a lot of folks out there:
Example:
I want to get a listing of all applicable rates for a delivery:
a rate can be defined in three ways:
a. the entire state
b. for a certain area code
c. for a specific zip
to make things worth ideally I want to be able to say all of state="CA" is
$2, but areacode="714" is $1
and zip="92649" is $.50
the tables look like this right now
city(zip varchar(10),areacode varchar(10),state varchar(2),name varchar(30))
rate(zip varchar(10),areacode varchar(10),state varchar(2),decimal(10,2))
Data
rate('92647','','',.5)
rate('','714','',1)
rate('','','CA',2)
I was thinking of maybe using the same logic as for the permission system
inside of MySql with % or something as wildcards but left it empty sting for
now. Which also leads me to another question as I would like to setup
permissions for various users to be able to maintain only certain rates.
This can be done on the application level. I am wondering if I need to OR
should create a query in the application doing the various or conditions or
if there is a more elegant solution.
Note: I left out the NOT NULL and the index definition.
Martin Hubert
Freightgate - New Dimensions in e-Logistics (sm)
Visit us at http://www.freightgate.com
Email: [EMAIL PROTECTED]
Phone: +714.799.2833 Fax: +714.799.0100
-
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 Design Question
Design Question
Hello,
I have a table design question relating to best practice and performance.
Monthy is somewhat of a SQL guru and so may be many others on this list,
this may actually help a lot of folks out there:
Example:
I want to get a listing of all applicable rates for a delivery:
a rate can be defined in three ways:
a. the entire state
b. for a certain area code
c. for a specific zip
to make things worth ideally I want to be able to say
carrier1:
all of state="CA" is $2,
but areacode="714" is $1
and zip="92649" is $.50
carrier2:
but areacode="310" is $1
and zip="92647" is $.50
and zip="92648" is $.60
and zip="92649" is $.70
the tables look like this right now
city(zip varchar(10),areacode varchar(10),state varchar(2),name varchar(30))
rate(zip varchar(10),areacode varchar(10),state varchar(2),carrier
varchar(5),decimal(10,2))
Data
rate('92647','','','c1',.5)
rate('','714','','c1',1)
rate('','','CA','c1',2)
rate('92647','','','c2',.5)
rate('92648','','','c2',.5)
rate('92649','','','c2',.5)
rate('','310','','c2',1)
if I now query for all rates in CA it would select * from city where
city.state="CA"
but what's the most elegant way to do the join to get it back as one
resultSet ?
I was thinking of maybe using the same logic as for the permission system
inside of MySql with % or something as wildcards but left it empty sting for
now. Which also leads me to another question as I would like to setup
permissions for various users to be able to maintain only certain rates.
This can be done on the application level. I am wondering if I need to OR
should create a query in the application doing the various or conditions or
if there is a more elegant solution.
Note: I left out the NOT NULL and the index definition.
Thanks SO MUCH
Martin Hubert
Freightgate - New Dimensions in e-Logistics (sm)
Visit us at http://www.freightgate.com
Email: [EMAIL PROTECTED]
Phone: +714.799.2833 Fax: +714.799.0100
-
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
