Fwd: Database design help
Looking for some help / comments if possible ? Cheers Neil -- Forwarded message -- From: Neil Tompkins neil.tompk...@googlemail.com Date: Tue, Aug 31, 2010 at 8:48 PM Subject: Database design help To: mysql@lists.mysql.com Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil
Re: Database design help
Hi there, I know you would like just a solution, but I want to give you just a little bit of background. Think in real life things(entities), think as you would have to do it on paper. [1] You said you have: managers, teams players and fixtures/results (matches) these are your tables plus teams! [2] then find what relationships you have between tables. (a) *manager* (have name and other properties) belongs to a team (b) *player* (have name and other properties) belong to a team (c) *team* (have name , have 1 manager, and other properties) play matches (d) *match* have results translate [have] with properties(columns with values of the table) translate other actions with relationships (columns with ids of other tables) in the case of *match* you would have: idteam1, idteam2, result at least. in the case of *player* you would have: idplayer, name, idteamat least in the case of *manager*, if you think a manager can manage more than one team you will use an idmanager in the team table otherwise you can use also an idteam in the manager table that allows more managers to manage the same team. I dont want (no time sorry!) to write here the data model, but I think this few lines can trigger the best idea in you. Claudio 2010/9/1 Tompkins Neil neil.tompk...@googlemail.com Looking for some help / comments if possible ? Cheers Neil -- Forwarded message -- From: Neil Tompkins neil.tompk...@googlemail.com Date: Tue, Aug 31, 2010 at 8:48 PM Subject: Database design help To: mysql@lists.mysql.com Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil -- Claudio
RE: Database design help
Hi Neil, May be your question is too vague. You have already identified the 'real world' objects that you want represented in the database. Have you identified the specific pieces of information that you want stored for each object ? After you do that, you can then start to see what the relationships between the objects are. And you can then ask people that don't know anything about your application more specific questions like: - this is what I have in this and that object : how do I get this and that to relate to this and that ? Thanks, Justin -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: 01 September 2010 12:52 To: [MySQL] Subject: Fwd: Database design help Looking for some help / comments if possible ? Cheers Neil -- Forwarded message -- From: Neil Tompkins neil.tompk...@googlemail.com Date: Tue, Aug 31, 2010 at 8:48 PM Subject: Database design help To: mysql@lists.mysql.com Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil __ Information from ESET NOD32 Antivirus, version of virus signature database 5414 (20100901) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5414 (20100901) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Database design help
I strongly suggest that you make a separate table for the manager - team relationship, so you can keep a history. Put a date-stamp in there. This might come in handy as you get further into your design. I ran into this problem when one of our sales reps moved from one office to another, and took their sales history with them! That was a mess to unscramble. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 31, 2010 3:48 PM To: mysql@lists.mysql.com Subject: Database design help Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Database design help
I do have a tabled which contains both the managers_id and teams_id for the current teams managed. I think by adding the managers_id alongside the fixture_result table will then allow me to find which points the manager has accumulated alongside which fixtures and teams. Cheers Neil On Wed, Sep 1, 2010 at 4:43 PM, Jerry Schwartz je...@gii.co.jp wrote: I strongly suggest that you make a separate table for the manager - team relationship, so you can keep a history. Put a date-stamp in there. This might come in handy as you get further into your design. I ran into this problem when one of our sales reps moved from one office to another, and took their sales history with them! That was a mess to unscramble. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 31, 2010 3:48 PM To: mysql@lists.mysql.com Subject: Database design help Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp
Re: Database design help
On 9/1/2010 11:47 AM, Tompkins Neil wrote: I do have a tabled which contains both the managers_id and teams_id for the current teams managed. I think by adding the managers_id alongside the fixture_result table will then allow me to find which points the manager has accumulated alongside which fixtures and teams. Cheers Neil On Wed, Sep 1, 2010 at 4:43 PM, Jerry Schwartz je...@gii.co.jp wrote: I strongly suggest that you make a separate table for the manager - team relationship, so you can keep a history. Put a date-stamp in there. This might come in handy as you get further into your design. I ran into this problem when one of our sales reps moved from one office to another, and took their sales history with them! That was a mess to unscramble. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 31, 2010 3:48 PM To: mysql@lists.mysql.com Subject: Database design help Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? I think you are definitely on the right track. Each score does not belong to just a manager or to a team but to a manger/team combination. Should the manager switch teams, those results need to remain associated to both entities not just the manager. here's a possible record shape: manager_id, team_id, game_id, ... summary details about the game ... This way each result is associated with the correct combination of entities (a manager and a team) and not just one or the other. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Database design help
Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Database design and query help
Hello, Currently, I have four tables (Items, UpdatePrice, UpdateStatus and UpdateRelease). All the Update tables are linked to Items.ItemID via Update(Price|Status|Release)ItemKey. Personally, I don't feel that this is the best database design I could have, but I can't seem to come up with one that'll work for me. I need to be able to add updates to all three cases and still have access to a history of updates. The problem(s) with this design is that I - in my opinion - get a very slow result. Items consists of 500+ rows at the moment and the same goes for the three Update tables. Those will obviously grow much quicker. Another problem is that I can't seem to sort on the Items table, while sorting on either of the Update tables seems for work. This is my query for gathering all the data I need from all four tables: SELECT * FROM Items t1 JOIN(SELECT * FROM UpdatePrice ORDER BY UpdatePrice.UpdatePriceID DESC) AS t2 ON t1.ItemID = t2.UpdatePriceItemKey JOIN(SELECT * FROM UpdateStatusORDER BY UpdateStatus.UpdateStatusID DESC) AS t3 ON t1.ItemID = t3.UpdateStatusItemKey JOIN(SELECT * FROM UpdateRelease ORDER BY UpdateRelease.UpdateReleaseID DESC) AS t4 ON t1.ItemID = t4.UpdateReleaseItemKey WHERE t1.ItemIsGame = 1 GROUP BY t1.ItemID and then SORT BY t1.ItemTitle (doesn't work) or SORT BY t2.UpdatePriceNew (does work). http://grab.by/BWW - Screenshot of the query in case formatting is lost in translation! Basically, my questions are: 1) Is this a poor database design? If yes, how would you do it? 2) Is this a bloated query which can be perfected to work as intended (mine doesn't) and perhaps faster? Sincerely, Eskil Kvalnes eskil.kval...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Questions on Database Design
Thanks to Martin and John for their help! Mark On Sat, Oct 3, 2009 at 5:53 PM, Martin Gainty mgai...@hotmail.com wrote: enforcing by username/password to the DB is your safest method and if you want to really be safe put ssh access onto the MySQL Server here is how to install SSH and MySQL onto Ubuntu http://ubuntuforums.org/showthread.php?t=388073 and to access SSHClient http://dev.mysql.com/doc/refman/5.1/en/windows-and-ssh.html HTH Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Sat, 3 Oct 2009 18:11:59 -0600 From: john.l.me...@gmail.com To: m...@phillipsmarketing.biz CC: mysql@lists.mysql.com Subject: Re: Questions on Database Design Mark Phillips wrote: On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty mgai...@hotmail.com wrote: depends on the relationship of the Data Tables and the Users that use them for instance if I was to setup a table of outgoing calls from 2 distinct individuals : Me calls to HarvardMedicalSchool, MassGeneral, SomervilleHospital and AMA VereinDesKrankRufscalls to Biff,Tony,EdSoprano and Destiny so as you can see the difference between my calls and Vereins calls should never be joined as Vereins customers are distinctly not mine and mine are not his Moreover my contact table would contain Degrees and titles where Vereins customers have no need for that So in this case it would make perfect sense for my Database to be separate and distinct from Vereins database..if for no other reason than the schemas are completely difference With an emphasis on security once Verein initiates populating his records on your DB by populating the same tables and using the same join relationships it will be impossible to force him to not use those tables or even to restrich his access to the slave server while you're updating the master You can restrict access by GRANT SELECT on the tables to Verein but that would last only a week or 2 until Verein requests update and insert access to the DB. Once the INSERT and UPDATE grants are made you wont be able to separate his records from yours Keep the 2 separate is my suggestion..MySQL is inexpensive and HW is cheap so this should be a low cost solution for you Keep us apprised and any feel free to inquire on any operational details you may require. Thanks! To make sure I understand. Even if the schemas are the same, if the data is not related, nor is meant to be combined in some way (eg rolled up or summed in some way), then creating a separate database for each user is a better way to go; or at least a meaningful way to go. A side benefit is greater security from the stand point that user a cannot get to user b's data. Can't I achieve the same level of security if each row has a userID, and all queries use a where userID=xxx clause? Mark no, don't confuse that with database security. There are too many ways to get around that sort of trick through SQL injection attacks. Read http://dev.mysql.com/doc/refman/5.4/en/privilege-system.html for a starter on privileges and security. But as long as you're not needing to regularly combine and aggregate the data then creating separate databases is a reasonable option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com -- Hotmail: Trusted email with powerful SPAM protection. Sign up now.http://clk.atdmt.com/GBL/go/177141665/direct/01/
Questions on Database Design
I am new at database design, and my question relates to the trade-offs between putting all data in one database or several for mysql. For example, say I have an application where a users login from their mobile phones and read/write data to a database. Say there are roughly 10-15 tables in the database and each user will add approximately 20,000 records per year. Each user should not have access to data from another user. Users have to register in some way to create their database in the first place. When does it make sense to give each user their own database versus putting all the data into one database (ie one set of tables) and with multiple userIDs? 10 users? 1,000 users? Never? Thanks! Mark
Re: Questions on Database Design
Mark Phillips wrote: I am new at database design, and my question relates to the trade-offs between putting all data in one database or several for mysql. For example, say I have an application where a users login from their mobile phones and read/write data to a database. Say there are roughly 10-15 tables in the database and each user will add approximately 20,000 records per year. Each user should not have access to data from another user. Users have to register in some way to create their database in the first place. When does it make sense to give each user their own database versus putting all the data into one database (ie one set of tables) and with multiple userIDs? 10 users? 1,000 users? Never? It's not so much how many users you have (though that may be a question of data storage more than databases) as to what are they doing? Are the actions related? If they are, then have one database with each user having access to their records and their records only, which can easily be done with terms of database security.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Questions on Database Design
On Sat, Oct 3, 2009 at 2:47 PM, John Meyer john.l.me...@gmail.com wrote: Mark Phillips wrote: I am new at database design, and my question relates to the trade-offs between putting all data in one database or several for mysql. For example, say I have an application where a users login from their mobile phones and read/write data to a database. Say there are roughly 10-15 tables in the database and each user will add approximately 20,000 records per year. Each user should not have access to data from another user. Users have to register in some way to create their database in the first place. When does it make sense to give each user their own database versus putting all the data into one database (ie one set of tables) and with multiple userIDs? 10 users? 1,000 users? Never? It's not so much how many users you have (though that may be a question of data storage more than databases) as to what are they doing? Are the actions related? If they are, then have one database with each user having access to their records and their records only, which can easily be done with terms of database security.. John, Thanks. The data is private to each user; there is no sharing of data. I am not sure what you mean by are the actions related Each user is reading/writing independently of each other. Would that argue for separate databases? Mark
Re: Questions on Database Design
John, Thanks. The data is private to each user; there is no sharing of data. I am not sure what you mean by are the actions related Each user is reading/writing independently of each other. Would that argue for separate databases? Mark Are the actions of a similar nature (i.e. they're all writing the same type of data and the databases themselves would be similar if not the same)? Is there any sort of application that would traverse all of those databases at once? Also keep in mind that multiple databases increases your complexity. I think we'd have a better idea if we knew a little more of the specifics of this application. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Questions on Database Design
On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty mgai...@hotmail.com wrote: depends on the relationship of the Data Tables and the Users that use them for instance if I was to setup a table of outgoing calls from 2 distinct individuals : Me calls to HarvardMedicalSchool, MassGeneral, SomervilleHospital and AMA VereinDesKrankRufscalls to Biff,Tony,EdSoprano and Destiny so as you can see the difference between my calls and Vereins calls should never be joined as Vereins customers are distinctly not mine and mine are not his Moreover my contact table would contain Degrees and titles where Vereins customers have no need for that So in this case it would make perfect sense for my Database to be separate and distinct from Vereins database..if for no other reason than the schemas are completely difference With an emphasis on security once Verein initiates populating his records on your DB by populating the same tables and using the same join relationships it will be impossible to force him to not use those tables or even to restrich his access to the slave server while you're updating the master You can restrict access by GRANT SELECT on the tables to Verein but that would last only a week or 2 until Verein requests update and insert access to the DB. Once the INSERT and UPDATE grants are made you wont be able to separate his records from yours Keep the 2 separate is my suggestion..MySQL is inexpensive and HW is cheap so this should be a low cost solution for you Keep us apprised and any feel free to inquire on any operational details you may require. Thanks! To make sure I understand. Even if the schemas are the same, if the data is not related, nor is meant to be combined in some way (eg rolled up or summed in some way), then creating a separate database for each user is a better way to go; or at least a meaningful way to go. A side benefit is greater security from the stand point that user a cannot get to user b's data. Can't I achieve the same level of security if each row has a userID, and all queries use a where userID=xxx clause? Mark Date: Sat, 3 Oct 2009 14:38:25 -0700 Subject: Questions on Database Design From: To: mysql@lists.mysql.com I am new at database design, and my question relates to the trade-offs between putting all data in one database or several for mysql. For example, say I have an application where a users login from their mobile phones and read/write data to a database. Say there are roughly 10-15 tables in the database and each user will add approximately 20,000 records per year. Each user should not have access to data from another user. Users have to register in some way to create their database in the first place. When does it make sense to give each user their own database versus putting all the data into one database (ie one set of tables) and with multiple userIDs? 10 users? 1,000 users? Never? Thanks! Mark -- Hotmail: Free, trusted and rich email service. Get it now.http://clk.atdmt.com/GBL/go/171222984/direct/01/
Re: Questions on Database Design
On Sat, Oct 3, 2009 at 4:02 PM, John Meyer john.l.me...@gmail.com wrote: John, Thanks. The data is private to each user; there is no sharing of data. I am not sure what you mean by are the actions related Each user is reading/writing independently of each other. Would that argue for separate databases? Mark Are the actions of a similar nature (i.e. they're all writing the same type of data and the databases themselves would be similar if not the same)? Each user will write the same type of data to the same schema. So the databases schemas would be identical. Is there any sort of application that would traverse all of those databases at once? Not really necessary from the user's perspective. Also keep in mind that multiple databases increases your complexity. I think we'd have a better idea if we knew a little more of the specifics of this application. Sure, no great military secrets here. The application is a mobile softball (baseball, basketball, soccer, etc.) score book. The data for each pitch (softball = pitch type, who made what play, what the batter did, errors, etc.) is entered on the cell phone, and stored in MySQL tables in order to create game and season stats for a team and each player. This can also apply to other sports. Each user is a team manager or scorekeeper. There really isn't any need for team A to see/access team B's stats. A league may want to do a special type of roll-up, but this app is really just for each team. I am sure an app could be written to do the roll-up, but that is not the main focus. I think by your discussion, it may make sense to have separate databases for each user instead of add a userID column to many of the tables to separate each user's data from the other users. Does that make sense? Mark
Re: Questions on Database Design
Mark Phillips wrote: On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty mgai...@hotmail.com wrote: depends on the relationship of the Data Tables and the Users that use them for instance if I was to setup a table of outgoing calls from 2 distinct individuals : Me calls to HarvardMedicalSchool, MassGeneral, SomervilleHospital and AMA VereinDesKrankRufscalls to Biff,Tony,EdSoprano and Destiny so as you can see the difference between my calls and Vereins calls should never be joined as Vereins customers are distinctly not mine and mine are not his Moreover my contact table would contain Degrees and titles where Vereins customers have no need for that So in this case it would make perfect sense for my Database to be separate and distinct from Vereins database..if for no other reason than the schemas are completely difference With an emphasis on security once Verein initiates populating his records on your DB by populating the same tables and using the same join relationships it will be impossible to force him to not use those tables or even to restrich his access to the slave server while you're updating the master You can restrict access by GRANT SELECT on the tables to Verein but that would last only a week or 2 until Verein requests update and insert access to the DB. Once the INSERT and UPDATE grants are made you wont be able to separate his records from yours Keep the 2 separate is my suggestion..MySQL is inexpensive and HW is cheap so this should be a low cost solution for you Keep us apprised and any feel free to inquire on any operational details you may require. Thanks! To make sure I understand. Even if the schemas are the same, if the data is not related, nor is meant to be combined in some way (eg rolled up or summed in some way), then creating a separate database for each user is a better way to go; or at least a meaningful way to go. A side benefit is greater security from the stand point that user a cannot get to user b's data. Can't I achieve the same level of security if each row has a userID, and all queries use a where userID=xxx clause? Mark no, don't confuse that with database security. There are too many ways to get around that sort of trick through SQL injection attacks. Read http://dev.mysql.com/doc/refman/5.4/en/privilege-system.html for a starter on privileges and security. But as long as you're not needing to regularly combine and aggregate the data then creating separate databases is a reasonable option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: database design
-Original Message- From: AndrewJames [mailto:andrewhu...@gmail.com] Sent: Saturday, September 12, 2009 1:20 AM To: Kyong Kim; Arthur Fuller Cc: Claudio Nanni; mysql Subject: Re: database design thank you all, i think You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. is my answer. [JS] I might have missed part of the discussion, but a foreign key back to an Article_Type table would help enforce data integrity. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- From: Kyong Kim kykim...@gmail.com Sent: Saturday, September 12, 2009 8:22 AM To: Arthur Fuller fuller.art...@gmail.com Cc: Claudio Nanni claudio.na...@gmail.com; AndrewJames andrewhu...@gmail.com; mysql mysql@lists.mysql.com Subject: Re: database design A) You would probably want to populate the Article.Article_Type column with Article_Type.ID. You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. I would also consider the use of natural primary key vs surrogate primary key. We've seen good results with primary key lookups on large tables (especially creating grouped subsets of data) If you imagine your data set growing fairly large, you should take a stab at projecting your workload to determine whether you would want to optimize access speed vs insert. For example, if you will be searching the article table by uid, you might want to cluster the data by uid so all related articles will be stored next to each other. Kyong On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller fuller.art...@gmail.com wrote: I agree with Claudio. You have your design correct. The only other thing you need is the uid qualifier. Presumably you are using PHP or some other front end to present your data. Your front end would request the user's name and password, saving the uid in a variable and then issuing the select with a WHERE clause that passes the uid in: select * from articles A left joing article_types AT on A.article_type = AT.Arcticle_types_id WHERE A.uid = insert your variable here hth, Arthur On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni claudio.na...@gmail.comwrote: A.J., It sounds good to me! You can be a little confused but you did it well, It seems you have all you need there. A) Yes B) select * from articles A left join article_types AT on A.article_type = AT.article_types_id Claudio 2009/9/11 AndrewJames andrewhu...@gmail.com This is a bit of a long shot, but i really need some help and or directed to the best reading resources. as i begun building my database (as i went along), i now realise i have to stop coding and sit back and design the database properly before i can go on. However i am still unable to wrap my head around what data to put into what tables, and which columns i need to link to make the relationships. so far, here is what i have. TABLES: users -uid(pk) -username -password articles -article_id(pk) -uid(fk) -article_type(fk) -article_subject -article_body article_types -article_types_id(pk) -article_type So i want the user to be able to login and add articles. I then want to be able to view all the articles the user has submitted. So in my understanding i need to link the users.uid(pk) to the articles.uid(fk) (so i know which user the article belongs to, please correct and update me if i am wrong) I am stuck at this point. A) Have i created the right tables and columns for each table, AND B) How do i link the articles.article_type to articles_type.type? (IF in fact that is even the correct linkage)?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: database design
Well, if you have a fixed number of article types, then maybe. If there is a chance of more types being added later, then no. Are you planning to hard code selection lists in your front end, or would you like to retrieve data from sql ??? Maybe a read up on Database Normalization is due: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html On Sat, September 12, 2009 07:19, AndrewJames wrote: thank you all, i think You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. is my answer. -- From: Kyong Kim kykim...@gmail.com Sent: Saturday, September 12, 2009 8:22 AM To: Arthur Fuller fuller.art...@gmail.com Cc: Claudio Nanni claudio.na...@gmail.com; AndrewJames andrewhu...@gmail.com; mysql mysql@lists.mysql.com Subject: Re: database design A) You would probably want to populate the Article.Article_Type column with Article_Type.ID. You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. I would also consider the use of natural primary key vs surrogate primary key. We've seen good results with primary key lookups on large tables (especially creating grouped subsets of data) If you imagine your data set growing fairly large, you should take a stab at projecting your workload to determine whether you would want to optimize access speed vs insert. For example, if you will be searching the article table by uid, you might want to cluster the data by uid so all related articles will be stored next to each other. Kyong On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller fuller.art...@gmail.com wrote: I agree with Claudio. You have your design correct. The only other thing you need is the uid qualifier. Presumably you are using PHP or some other front end to present your data. Your front end would request the user's name and password, saving the uid in a variable and then issuing the select with a WHERE clause that passes the uid in: select * from articles A left joing article_types AT on A.article_type = AT.Arcticle_types_id WHERE A.uid = insert your variable here hth, Arthur On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni claudio.na...@gmail.comwrote: A.J., It sounds good to me! You can be a little confused but you did it well, It seems you have all you need there. A) Yes B) select * from articles A left join article_types AT on A.article_type = AT.article_types_id Claudio 2009/9/11 AndrewJames andrewhu...@gmail.com This is a bit of a long shot, but i really need some help and or directed to the best reading resources. as i begun building my database (as i went along), i now realise i have to stop coding and sit back and design the database properly before i can go on. However i am still unable to wrap my head around what data to put into what tables, and which columns i need to link to make the relationships. so far, here is what i have. TABLES: users -uid(pk) -username -password articles -article_id(pk) -uid(fk) -article_type(fk) -article_subject -article_body article_types -article_types_id(pk) -article_type So i want the user to be able to login and add articles. I then want to be able to view all the articles the user has submitted. So in my understanding i need to link the users.uid(pk) to the articles.uid(fk) (so i know which user the article belongs to, please correct and update me if i am wrong) I am stuck at this point. A) Have i created the right tables and columns for each table, AND B) How do i link the articles.article_type to articles_type.type? (IF in fact that is even the correct linkage)?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mog...@fumlersoft.dk -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
R: RE: database design
Manageability. Id(surrogate) can be autoincrement so managed by the engine, Joins are simpler to write. Imagine a natural key composed of three or more fields. Practical, with surrogate you can 'book' a record while not knowing yet all of the values that compose the natural key. Just a couple of points. Claudio Il giorno 12 set, 2009 12:57 m., Martin Gainty mgai...@hotmail.com ha scritto: what are the advantages to implementing a natural key over surrogate key ? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Fri, 11 Sep 2009 15:22:57 -0700 Subject: Re: database design From: kykim...@gmail.com To: fuller.art...@gmail.com CC: claudio.na...@gmail.com; andrewhu...@gmail.com; mysql@lists.mysql.com A) You would probably want to populate the Article.Article_Type column with Article_Type.ID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com -- Get back to school stuff for them and cashback for you. Try Bing now.http://www.bing.com/cashback?form=MSHYCBpubl=WLHMTAGcrea=TEXT_MSHYCB_BackToSchool_Cashback_BTSCashback_1x1
Re: database design
Storing it directly will cause problems when you want to add a new Article Type. IMO it's better to have an ArticleTypes table (AutoIncrement) and store its values in the ArticleTypeID column in the Articles table. A. On Sat, Sep 12, 2009 at 1:19 AM, AndrewJames andrewhu...@gmail.com wrote: thank you all, i think You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. is my answer.
database design
This is a bit of a long shot, but i really need some help and or directed to the best reading resources. as i begun building my database (as i went along), i now realise i have to stop coding and sit back and design the database properly before i can go on. However i am still unable to wrap my head around what data to put into what tables, and which columns i need to link to make the relationships. so far, here is what i have. TABLES: users -uid(pk) -username -password articles -article_id(pk) -uid(fk) -article_type(fk) -article_subject -article_body article_types -article_types_id(pk) -article_type So i want the user to be able to login and add articles. I then want to be able to view all the articles the user has submitted. So in my understanding i need to link the users.uid(pk) to the articles.uid(fk) (so i know which user the article belongs to, please correct and update me if i am wrong) I am stuck at this point. A) Have i created the right tables and columns for each table, AND B) How do i link the articles.article_type to articles_type.type? (IF in fact that is even the correct linkage)?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: database design
A.J., It sounds good to me! You can be a little confused but you did it well, It seems you have all you need there. A) Yes B) select * from articles A left join article_types AT on A.article_type = AT.article_types_id Claudio 2009/9/11 AndrewJames andrewhu...@gmail.com This is a bit of a long shot, but i really need some help and or directed to the best reading resources. as i begun building my database (as i went along), i now realise i have to stop coding and sit back and design the database properly before i can go on. However i am still unable to wrap my head around what data to put into what tables, and which columns i need to link to make the relationships. so far, here is what i have. TABLES: users -uid(pk) -username -password articles -article_id(pk) -uid(fk) -article_type(fk) -article_subject -article_body article_types -article_types_id(pk) -article_type So i want the user to be able to login and add articles. I then want to be able to view all the articles the user has submitted. So in my understanding i need to link the users.uid(pk) to the articles.uid(fk) (so i know which user the article belongs to, please correct and update me if i am wrong) I am stuck at this point. A) Have i created the right tables and columns for each table, AND B) How do i link the articles.article_type to articles_type.type? (IF in fact that is even the correct linkage)?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: database design
I agree with Claudio. You have your design correct. The only other thing you need is the uid qualifier. Presumably you are using PHP or some other front end to present your data. Your front end would request the user's name and password, saving the uid in a variable and then issuing the select with a WHERE clause that passes the uid in: select * from articles A left joing article_types AT on A.article_type = AT.Arcticle_types_id WHERE A.uid = insert your variable here hth, Arthur On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni claudio.na...@gmail.comwrote: A.J., It sounds good to me! You can be a little confused but you did it well, It seems you have all you need there. A) Yes B) select * from articles A left join article_types AT on A.article_type = AT.article_types_id Claudio 2009/9/11 AndrewJames andrewhu...@gmail.com This is a bit of a long shot, but i really need some help and or directed to the best reading resources. as i begun building my database (as i went along), i now realise i have to stop coding and sit back and design the database properly before i can go on. However i am still unable to wrap my head around what data to put into what tables, and which columns i need to link to make the relationships. so far, here is what i have. TABLES: users -uid(pk) -username -password articles -article_id(pk) -uid(fk) -article_type(fk) -article_subject -article_body article_types -article_types_id(pk) -article_type So i want the user to be able to login and add articles. I then want to be able to view all the articles the user has submitted. So in my understanding i need to link the users.uid(pk) to the articles.uid(fk) (so i know which user the article belongs to, please correct and update me if i am wrong) I am stuck at this point. A) Have i created the right tables and columns for each table, AND B) How do i link the articles.article_type to articles_type.type? (IF in fact that is even the correct linkage)?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: database design
A) You would probably want to populate the Article.Article_Type column with Article_Type.ID. You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. I would also consider the use of natural primary key vs surrogate primary key. We've seen good results with primary key lookups on large tables (especially creating grouped subsets of data) If you imagine your data set growing fairly large, you should take a stab at projecting your workload to determine whether you would want to optimize access speed vs insert. For example, if you will be searching the article table by uid, you might want to cluster the data by uid so all related articles will be stored next to each other. Kyong On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller fuller.art...@gmail.com wrote: I agree with Claudio. You have your design correct. The only other thing you need is the uid qualifier. Presumably you are using PHP or some other front end to present your data. Your front end would request the user's name and password, saving the uid in a variable and then issuing the select with a WHERE clause that passes the uid in: select * from articles A left joing article_types AT on A.article_type = AT.Arcticle_types_id WHERE A.uid = insert your variable here hth, Arthur On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni claudio.na...@gmail.comwrote: A.J., It sounds good to me! You can be a little confused but you did it well, It seems you have all you need there. A) Yes B) select * from articles A left join article_types AT on A.article_type = AT.article_types_id Claudio 2009/9/11 AndrewJames andrewhu...@gmail.com This is a bit of a long shot, but i really need some help and or directed to the best reading resources. as i begun building my database (as i went along), i now realise i have to stop coding and sit back and design the database properly before i can go on. However i am still unable to wrap my head around what data to put into what tables, and which columns i need to link to make the relationships. so far, here is what i have. TABLES: users -uid(pk) -username -password articles -article_id(pk) -uid(fk) -article_type(fk) -article_subject -article_body article_types -article_types_id(pk) -article_type So i want the user to be able to login and add articles. I then want to be able to view all the articles the user has submitted. So in my understanding i need to link the users.uid(pk) to the articles.uid(fk) (so i know which user the article belongs to, please correct and update me if i am wrong) I am stuck at this point. A) Have i created the right tables and columns for each table, AND B) How do i link the articles.article_type to articles_type.type? (IF in fact that is even the correct linkage)?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: database design
thank you all, i think You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. is my answer. -- From: Kyong Kim kykim...@gmail.com Sent: Saturday, September 12, 2009 8:22 AM To: Arthur Fuller fuller.art...@gmail.com Cc: Claudio Nanni claudio.na...@gmail.com; AndrewJames andrewhu...@gmail.com; mysql mysql@lists.mysql.com Subject: Re: database design A) You would probably want to populate the Article.Article_Type column with Article_Type.ID. You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. I would also consider the use of natural primary key vs surrogate primary key. We've seen good results with primary key lookups on large tables (especially creating grouped subsets of data) If you imagine your data set growing fairly large, you should take a stab at projecting your workload to determine whether you would want to optimize access speed vs insert. For example, if you will be searching the article table by uid, you might want to cluster the data by uid so all related articles will be stored next to each other. Kyong On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller fuller.art...@gmail.com wrote: I agree with Claudio. You have your design correct. The only other thing you need is the uid qualifier. Presumably you are using PHP or some other front end to present your data. Your front end would request the user's name and password, saving the uid in a variable and then issuing the select with a WHERE clause that passes the uid in: select * from articles A left joing article_types AT on A.article_type = AT.Arcticle_types_id WHERE A.uid = insert your variable here hth, Arthur On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni claudio.na...@gmail.comwrote: A.J., It sounds good to me! You can be a little confused but you did it well, It seems you have all you need there. A) Yes B) select * from articles A left join article_types AT on A.article_type = AT.article_types_id Claudio 2009/9/11 AndrewJames andrewhu...@gmail.com This is a bit of a long shot, but i really need some help and or directed to the best reading resources. as i begun building my database (as i went along), i now realise i have to stop coding and sit back and design the database properly before i can go on. However i am still unable to wrap my head around what data to put into what tables, and which columns i need to link to make the relationships. so far, here is what i have. TABLES: users -uid(pk) -username -password articles -article_id(pk) -uid(fk) -article_type(fk) -article_subject -article_body article_types -article_types_id(pk) -article_type So i want the user to be able to login and add articles. I then want to be able to view all the articles the user has submitted. So in my understanding i need to link the users.uid(pk) to the articles.uid(fk) (so i know which user the article belongs to, please correct and update me if i am wrong) I am stuck at this point. A) Have i created the right tables and columns for each table, AND B) How do i link the articles.article_type to articles_type.type? (IF in fact that is even the correct linkage)?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Database design - help
Again, please forgive my total ignorance. My ERD shows that the web links (URL table) are connected, via the sub-categories (SubCat table), to the main categories (Categories table). Is this correct for what I am trying to achieve ? Or should I also link the URL table to the Categories table ? Have been trying to create the ER Diagram with MySQL Workbench, and getting very frustrated. So far I have this www.ProBowlUK.co.uk/files/ERD_00.pdf cheers - Original Message - From: Martin Gainty To: bobsh...@ntlworld.com ; mysql@lists.mysql.com Cc: john.l.me...@gmail.com Sent: Friday, September 04, 2009 6:09 PM Subject: RE: Database design - help given the following table layouts URLs: URL_ID (primary key for URL) URL_TEXT URL_CATEGORY URL_ID (key which points to URL.URL_ID) CATEGORY_ID (key which points to CATEGORY.CATEGORY_ID) SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) CATEGORY CATEGORY_ID (primary Key for Category) CATEGORY_TEXT SUBCAT SUBCAT_ID(concatenated key for SubCat) CATEGORY_ID (concatenated key for Subcat) SUBCAT_TEXTso the diagram would look something like like URL_CATEGORY Table (URL Table) (CATEGORY TABLE) URL_ID1-1 URL.URL_ID CATEGORY.CATEGORY_ID1---1CATEGORY_IDURL_TEXT 1 ↓ 1 SUBCAT.CATEGORY_ID SUBCAT.SUBCAT_TEXT this is labour-intensive work that every DBA must perform to create a Database Martin Gainty __ From: bobsh...@ntlworld.com To: mysql@lists.mysql.com CC: john.l.me...@gmail.com Subject: Re: Database design - help Date: Fri, 4 Sep 2009 16:24:22 +0100 Hi Thanks for all the responses. However I am still stuck for a MySQL db I can create and code in PHP. Attached is a brief example of data to be used. One problem I have is with providing a listing that includes ... WTBC (Category without SubCats) and the 3 Zones (also, Cats without SubCats ??? ) (This is for a complete WTBC listing, in practice it may list depending on selected Zone) The example Schema is interesting, but is there another way of storing all links in one table and join them to Category and SubCat tables ? An example of the ER Diagram would also be helpful to me. cheers -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13901 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.77/2346 - Release Date: 09/04/09 17:51:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Database design - help
Thanks Not sure how I'm reading this, but shouldn't the URL be linked to SubCategory ? - Original Message - From: Martin Gainty To: bobsh...@ntlworld.com ; mysql@lists.mysql.com Cc: john.l.me...@gmail.com Sent: Friday, September 04, 2009 6:09 PM Subject: RE: Database design - help given the following table layouts URLs: URL_ID (primary key for URL) URL_TEXT URL_CATEGORY URL_ID (key which points to URL.URL_ID) CATEGORY_ID (key which points to CATEGORY.CATEGORY_ID) SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) CATEGORY CATEGORY_ID (primary Key for Category) CATEGORY_TEXT SUBCAT SUBCAT_ID (concatenated key for SubCat) CATEGORY_ID (concatenated key for Subcat) SUBCAT_TEXTso the diagram would look something like like URL_CATEGORY Table (URL Table) (CATEGORY TABLE) URL_ID1-1 URL.URL_ID CATEGORY.CATEGORY_ID1---1CATEGORY_IDURL_TEXT 1 ↓ 1 SUBCAT.CATEGORY_ID SUBCAT.SUBCAT_TEXT this is labour-intensive work that every DBA must perform to create a Database Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: bobsh...@ntlworld.com To: mysql@lists.mysql.com CC: john.l.me...@gmail.com Subject: Re: Database design - help Date: Fri, 4 Sep 2009 16:24:22 +0100 Hi Thanks for all the responses. However I am still stuck for a MySQL db I can create and code in PHP. Attached is a brief example of data to be used. One problem I have is with providing a listing that includes ... WTBC (Category without SubCats) and the 3 Zones (also, Cats without SubCats ??? ) (This is for a complete WTBC listing, in practice it may list depending on selected Zone) The example Schema is interesting, but is there another way of storing all links in one table and join them to Category and SubCat tables ? An example of the ER Diagram would also be helpful to me. cheers - Original Message - From: John Meyer john.l.me...@gmail.com To: BobSharp bobsh...@ntlworld.com Cc: mysql@lists.mysql.com Sent: Monday, August 31, 2009 4:56 PM Subject: Re: Database design - help BobSharp wrote: As a complete newbie in MySQL, I need a database to store URLs related to Tenpin Bowling. There are several Categories ... Equipment Manufacturers, Organistations, (UK) ProShops, (UK) Bowling Centres, Personal Websites, Misc., Coaching Instructional websites, etc. There will be some sub-categories. eg: Organistions will have ... Zones of WTBC, National Organisations within the Zones, UK organisations, Disabled Bowling organisations, ... eg: Personal Website might have ... Bowler's, Pro Bowler's, Leagues, etc. Can anyone suggest how I should set out tables for this database ? Here's one suggestion Table: URLs: URL_ID URL_TEXT CATEGORY CATEGORY_ID CATEGORY_TEXT SUBCAT SUBCAT_ID CATEGORY_ID SUBCAT_TEXT URL_CATEGORY URL_ID CATEGORY_ID SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.72/2337 - Release Date: 08/31/09 05:50:00 -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13901 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- Windows Live: Keep your friends up to date with what you do online. Find out more
Re: Database design - help
Please forgive my total ignorance. URL-Category linking ... with Foriegn Keys or Primary Keys ? Have been trying to create the ER Diagram with MySQL Workbench, and getting very frustrated. cheers - Original Message - From: Martin Gainty To: bobsh...@ntlworld.com ; mysql@lists.mysql.com Cc: john.l.me...@gmail.com Sent: Friday, September 04, 2009 6:09 PM Subject: RE: Database design - help given the following table layouts URLs: URL_ID (primary key for URL) URL_TEXT URL_CATEGORY URL_ID (key which points to URL.URL_ID) CATEGORY_ID (key which points to CATEGORY.CATEGORY_ID) SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) CATEGORY CATEGORY_ID (primary Key for Category) CATEGORY_TEXT SUBCAT SUBCAT_ID (concatenated key for SubCat) CATEGORY_ID (concatenated key for Subcat) SUBCAT_TEXTso the diagram would look something like like URL_CATEGORY Table (URL Table) (CATEGORY TABLE) URL_ID1-1 URL.URL_ID CATEGORY.CATEGORY_ID1---1CATEGORY_IDURL_TEXT 1 ↓ 1 SUBCAT.CATEGORY_ID SUBCAT.SUBCAT_TEXT this is labour-intensive work that every DBA must perform to create a Database Martin Gainty __ From: bobsh...@ntlworld.com To: mysql@lists.mysql.com CC: john.l.me...@gmail.com Subject: Re: Database design - help Date: Fri, 4 Sep 2009 16:24:22 +0100 Hi Thanks for all the responses. However I am still stuck for a MySQL db I can create and code in PHP. Attached is a brief example of data to be used. One problem I have is with providing a listing that includes ... WTBC (Category without SubCats) and the 3 Zones (also, Cats without SubCats ??? ) (This is for a complete WTBC listing, in practice it may list depending on selected Zone) The example Schema is interesting, but is there another way of storing all links in one table and join them to Category and SubCat tables ? An example of the ER Diagram would also be helpful to me. cheers -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13901 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Re: Database design - help
Hi Thanks for all the responses. However I am still stuck for a MySQL db I can create and code in PHP. Attached is a brief example of data to be used. One problem I have is with providing a listing that includes ... WTBC (Category without SubCats) and the 3 Zones (also, Cats without SubCats ??? ) (This is for a complete WTBC listing, in practice it may list depending on selected Zone) The example Schema is interesting, but is there another way of storing all links in one table and join them to Category and SubCat tables ? An example of the ER Diagram would also be helpful to me. cheers - Original Message - From: John Meyer john.l.me...@gmail.com To: BobSharp bobsh...@ntlworld.com Cc: mysql@lists.mysql.com Sent: Monday, August 31, 2009 4:56 PM Subject: Re: Database design - help BobSharp wrote: As a complete newbie in MySQL, I need a database to store URLs related to Tenpin Bowling. There are several Categories ... Equipment Manufacturers, Organistations, (UK) ProShops, (UK) Bowling Centres, Personal Websites, Misc., Coaching Instructional websites, etc. There will be some sub-categories. eg: Organistions will have ... Zones of WTBC, National Organisations within the Zones, UK organisations, Disabled Bowling organisations, ... eg: Personal Website might have ... Bowler's, Pro Bowler's, Leagues, etc. Can anyone suggest how I should set out tables for this database ? Here's one suggestion Table: URLs: URL_ID URL_TEXT CATEGORY CATEGORY_ID CATEGORY_TEXT SUBCAT SUBCAT_ID CATEGORY_ID SUBCAT_TEXT URL_CATEGORY URL_ID CATEGORY_ID SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.72/2337 - Release Date: 08/31/09 05:50:00 -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13901 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Database design - help
given the following table layouts URLs: URL_ID (primary key for URL) URL_TEXT URL_CATEGORY URL_ID (key which points to URL.URL_ID) CATEGORY_ID (key which points to CATEGORY.CATEGORY_ID) SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) CATEGORY CATEGORY_ID (primary Key for Category) CATEGORY_TEXT SUBCAT SUBCAT_ID(concatenated key for SubCat) CATEGORY_ID (concatenated key for Subcat) SUBCAT_TEXT so the diagram would look something like like URL_CATEGORY Table (URL Table) (CATEGORY TABLE)URL_ID1-1 URL.URL_ID CATEGORY.CATEGORY_ID1---1CATEGORY_IDURL_TEXT 1 ↓ 1 SUBCAT.CATEGORY_ID SUBCAT.SUBCAT_TEXT this is labour-intensive work that every DBA must perform to create a Database Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: bobsh...@ntlworld.com To: mysql@lists.mysql.com CC: john.l.me...@gmail.com Subject: Re: Database design - help Date: Fri, 4 Sep 2009 16:24:22 +0100 Hi Thanks for all the responses. However I am still stuck for a MySQL db I can create and code in PHP. Attached is a brief example of data to be used. One problem I have is with providing a listing that includes ... WTBC (Category without SubCats) and the 3 Zones (also, Cats without SubCats ??? ) (This is for a complete WTBC listing, in practice it may list depending on selected Zone) The example Schema is interesting, but is there another way of storing all links in one table and join them to Category and SubCat tables ? An example of the ER Diagram would also be helpful to me. cheers - Original Message - From: John Meyer john.l.me...@gmail.com To: BobSharp bobsh...@ntlworld.com Cc: mysql@lists.mysql.com Sent: Monday, August 31, 2009 4:56 PM Subject: Re: Database design - help BobSharp wrote: As a complete newbie in MySQL, I need a database to store URLs related to Tenpin Bowling. There are several Categories ... Equipment Manufacturers, Organistations, (UK) ProShops, (UK) Bowling Centres, Personal Websites, Misc., Coaching Instructional websites, etc. There will be some sub-categories. eg: Organistions will have ... Zones of WTBC, National Organisations within the Zones, UK organisations, Disabled Bowling organisations, ... eg: Personal Website might have ... Bowler's, Pro Bowler's, Leagues, etc. Can anyone suggest how I should set out tables for this database ? Here's one suggestion Table: URLs: URL_ID URL_TEXT CATEGORY CATEGORY_ID CATEGORY_TEXT SUBCAT SUBCAT_ID CATEGORY_ID SUBCAT_TEXT URL_CATEGORY URL_ID CATEGORY_ID SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.72/2337 - Release Date: 08/31/09 05:50:00 -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13901 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message _ Windows Live: Keep your friends up to date with what you do online. http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:ON:WL:en-US:SI_SB_online:082009
Database design - help
As a complete newbie in MySQL, I need a database to store URLs related to Tenpin Bowling. There are several Categories ... Equipment Manufacturers, Organistations, (UK) ProShops, (UK) Bowling Centres, Personal Websites, Misc., Coaching Instructional websites, etc. There will be some sub-categories. eg: Organistions will have ... Zones of WTBC, National Organisations within the Zones, UK organisations, Disabled Bowling organisations, ... eg: Personal Website might have ... Bowler's, Pro Bowler's, Leagues, etc. Can anyone suggest how I should set out tables for this database ? -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13876 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Database design - help
BobSharp wrote: As a complete newbie in MySQL, I need a database to store URLs related to Tenpin Bowling. There are several Categories ... Equipment Manufacturers, Organistations, (UK) ProShops, (UK) Bowling Centres, Personal Websites, Misc., Coaching Instructional websites, etc. There will be some sub-categories. eg: Organistions will have ... Zones of WTBC, National Organisations within the Zones, UK organisations, Disabled Bowling organisations, ... eg: Personal Website might have ... Bowler's, Pro Bowler's, Leagues, etc. Can anyone suggest how I should set out tables for this database ? Here's one suggestion Table: URLs: URL_ID URL_TEXT CATEGORY CATEGORY_ID CATEGORY_TEXT SUBCAT SUBCAT_ID CATEGORY_ID SUBCAT_TEXT URL_CATEGORY URL_ID CATEGORY_ID SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
database design
hi all, after a little 'research' and consultation with friends, i come up with these tables for my database project. table Item(ItemID,warehouse,ShapeID,weight,category,description) table Shape(ShapeID,physical shape) table Dimension(DimensionID,dimension) table ShapeDimension(ShapeDimensionID,ShapeID,DimensionID) table ItemShapeDimension(ItemID,ShapeDimensionID,value) ItemID,ShapeID,DimensionID, and ShapeDimensionID are primary keys, auto increment Shape Dimension are static tables Shape: Round, Hex, Angle, Channel, Pipe,..etc. Dimension: Diameter, Length, Width, Depth, Inner Diameter, Outer Diameter...etc. do you have any comment for this??? is there any potential structure problem with these relationship? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design
DEFAULT CHARSET=latin1; CREATE TABLE `garment_to_description` ( `garment_id` smallint(5) unsigned NOT NULL, `desc_id` smallint(5) unsigned NOT NULL, PRIMARY KEY (`garment_id`,`desc_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `garment_to_size` ( `garment_id` smallint(5) unsigned NOT NULL, `size_id` smallint(5) unsigned NOT NULL, PRIMARY KEY (`garment_id`,`size_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `garment_to_title` ( `garment_id` smallint(5) unsigned NOT NULL, `title_id` smallint(5) unsigned NOT NULL, PRIMARY KEY (`garment_id`,`title_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `garment_type` ( `type_id` smallint(5) unsigned NOT NULL auto_increment, `supplier_id` smallint(5) unsigned NOT NULL, `type` varchar(30) NOT NULL, PRIMARY KEY (`type_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE `sizes` ( `size_id` smallint(5) unsigned NOT NULL auto_increment, `supplier_id` smallint(5) unsigned NOT NULL, `type_id` smallint(5) unsigned NOT NULL, `size` varchar(15) NOT NULL, PRIMARY KEY (`size_id`), UNIQUE KEY `size` (`size`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE `suppliers` ( `supplier_id` smallint(5) unsigned NOT NULL auto_increment, `supplier` varchar(30) NOT NULL, PRIMARY KEY (`supplier_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE `title` ( `title_id` smallint(5) unsigned NOT NULL auto_increment, `title` varchar(60) NOT NULL, PRIMARY KEY (`title_id`), UNIQUE KEY `title` (`title`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; Thanks for any continued support. From: John Meyer [EMAIL PROTECTED] Date: Wed, 23 May 2007 07:28:23 -0600 To: mysql@lists.mysql.com Subject: Re: Database design Officelink wrote: Hi everyone, I¹m trying to set up a database with information that will be used in a garment slideshow in flash. The information to be included as part of the slideshow would be: code, optional title, description, colours, sizes, garment image, fabric swatch image Each clothing item to be included in the slideshow will belong to one of eleven or so categories. Each of the categories will belong to one of two category types. I also planned to set up a simple CMS that would allow the information to be added, edited and deleted from the database. With the above goals in mind, I came up with two tables as follows: GARMENTS TABLE garment_id, int(11), not null, auto_increment, primary key cat_id, int(10), unsigned, not null, default 0 garment_code, varchar(30), not null garment_title, varchar(40), null garment_desc, varchar(255), not null garment_image, varchar(50), not null garment_colour, varchar(50), not null garment_swatch, varchar(50), null garment_sizes, varchar(100), not null CATEGORIES TABLE cat_id, int(10), not null, auto_increment, primary key cat_name, varchar(40), not null cat_type, tinyint(4), not null, default 1 I was worried about repeating data in some of the columns, for example the garment_desc column would have information about sleeve length, cuff type, fabric, fabric composition etc. and I thought that all these areas could possibly be broken up into separate tables, but I wasn¹t sure if it was necessary. Also the colour and size columns would have a lot of repetitive data. While normalization does have the goal of eliminating repetition, there are other reasons. Most notably, you don't want to introduce errors or even differences into your database. A person who accidentally types eRd, for instance. You might, and I emphasize the word might, consider breaking color and size into two different tables based upon the following: 1. The possible set of valid answers. 2. Whether that element will be used in any sort of grouping or searching level (are you able to search by color, for instance) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database design
Hi everyone, I¹m trying to set up a database with information that will be used in a garment slideshow in flash. The information to be included as part of the slideshow would be: code, optional title, description, colours, sizes, garment image, fabric swatch image Each clothing item to be included in the slideshow will belong to one of eleven or so categories. Each of the categories will belong to one of two category types. I also planned to set up a simple CMS that would allow the information to be added, edited and deleted from the database. With the above goals in mind, I came up with two tables as follows: GARMENTS TABLE garment_id, int(11), not null, auto_increment, primary key cat_id, int(10), unsigned, not null, default 0 garment_code, varchar(30), not null garment_title, varchar(40), null garment_desc, varchar(255), not null garment_image, varchar(50), not null garment_colour, varchar(50), not null garment_swatch, varchar(50), null garment_sizes, varchar(100), not null CATEGORIES TABLE cat_id, int(10), not null, auto_increment, primary key cat_name, varchar(40), not null cat_type, tinyint(4), not null, default 1 I was worried about repeating data in some of the columns, for example the garment_desc column would have information about sleeve length, cuff type, fabric, fabric composition etc. and I thought that all these areas could possibly be broken up into separate tables, but I wasn¹t sure if it was necessary. Also the colour and size columns would have a lot of repetitive data. Someone indicated that normalization is not about eliminating repetition, it¹s about ensuring that the non-key attributes are functionally dependent on the entire primary key, but then I read somewhere that you¹re supposed to break down the information as far as possible to avoid redundancy so I¹m a bit confused. Or does it depend on the situation and what¹s required of the database. I mean say the CMS needed to have more functionality than what I indicated above I mean say the client wanted to be able to generate reports based on style information such as fabric composition or sleeve style etc. - would this change the setup? I wondered if someone could comment on the setup to see if I¹m on the right track here? Appreciate any help.
Re: Database design
Officelink wrote: Hi everyone, I¹m trying to set up a database with information that will be used in a garment slideshow in flash. The information to be included as part of the slideshow would be: code, optional title, description, colours, sizes, garment image, fabric swatch image Each clothing item to be included in the slideshow will belong to one of eleven or so categories. Each of the categories will belong to one of two category types. I also planned to set up a simple CMS that would allow the information to be added, edited and deleted from the database. With the above goals in mind, I came up with two tables as follows: GARMENTS TABLE garment_id, int(11), not null, auto_increment, primary key cat_id, int(10), unsigned, not null, default 0 garment_code, varchar(30), not null garment_title, varchar(40), null garment_desc, varchar(255), not null garment_image, varchar(50), not null garment_colour, varchar(50), not null garment_swatch, varchar(50), null garment_sizes, varchar(100), not null CATEGORIES TABLE cat_id, int(10), not null, auto_increment, primary key cat_name, varchar(40), not null cat_type, tinyint(4), not null, default 1 I was worried about repeating data in some of the columns, for example the garment_desc column would have information about sleeve length, cuff type, fabric, fabric composition etc. and I thought that all these areas could possibly be broken up into separate tables, but I wasn¹t sure if it was necessary. Also the colour and size columns would have a lot of repetitive data. While normalization does have the goal of eliminating repetition, there are other reasons. Most notably, you don't want to introduce errors or even differences into your database. A person who accidentally types eRd, for instance. You might, and I emphasize the word might, consider breaking color and size into two different tables based upon the following: 1. The possible set of valid answers. 2. Whether that element will be used in any sort of grouping or searching level (are you able to search by color, for instance) -- The NCP Revue -- http://www.ncprevue.com/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design
Normalization is about using ids to minimize change, which also eliminates repetition. It's fine to have the color red repeated throughout your table as long as it will never change. But if you suddenly have two shades of red, you'll need to update all the records that say red. If you used id's, you just update the text associated with the id, a single record. Nobody ever designs to 5th normal form (except as an exercise), you usually reach level 2 or 3. When designing a database, you want to determine the various objects you need to hold and their attributes. One mistake is that you are putting the garment attributes in the a general description field. Which is fine if you don't need to search on more than free form text. Sleeve, fabric, cuff, colors, etc. are all attributes of the garment. Since a garment can have multiple attributes, the attributes should be in a separate table. I would create an attributes table that contains all the attributes of the garment. Then you would be able to search the single table to find all garments made of a certain fabric with a certain cuff type. The garment attributes table contains codes that link to a description. The description could have multiple fields so you can handle conversions between different markets. For example, sizes vary between different geographic areas (my sneaker has sizes on the label for US, UK, EU and CM). Since the size is represented by an ID, you can search on any of the sizes the ID represents. - Original Message - From: Officelink [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 23, 2007 8:29 AM Subject: Database design Hi everyone, I¹m trying to set up a database with information that will be used in a garment slideshow in flash. The information to be included as part of the slideshow would be: code, optional title, description, colours, sizes, garment image, fabric swatch image Each clothing item to be included in the slideshow will belong to one of eleven or so categories. Each of the categories will belong to one of two category types. I also planned to set up a simple CMS that would allow the information to be added, edited and deleted from the database. With the above goals in mind, I came up with two tables as follows: GARMENTS TABLE garment_id, int(11), not null, auto_increment, primary key cat_id, int(10), unsigned, not null, default 0 garment_code, varchar(30), not null garment_title, varchar(40), null garment_desc, varchar(255), not null garment_image, varchar(50), not null garment_colour, varchar(50), not null garment_swatch, varchar(50), null garment_sizes, varchar(100), not null CATEGORIES TABLE cat_id, int(10), not null, auto_increment, primary key cat_name, varchar(40), not null cat_type, tinyint(4), not null, default 1 I was worried about repeating data in some of the columns, for example the garment_desc column would have information about sleeve length, cuff type, fabric, fabric composition etc. and I thought that all these areas could possibly be broken up into separate tables, but I wasn¹t sure if it was necessary. Also the colour and size columns would have a lot of repetitive data. Someone indicated that normalization is not about eliminating repetition, it¹s about ensuring that the non-key attributes are functionally dependent on the entire primary key, but then I read somewhere that you¹re supposed to break down the information as far as possible to avoid redundancy so I¹m a bit confused. Or does it depend on the situation and what¹s required of the database. I mean say the CMS needed to have more functionality than what I indicated above I mean say the client wanted to be able to generate reports based on style information such as fabric composition or sleeve style etc. - would this change the setup? I wondered if someone could comment on the setup to see if I¹m on the right track here? Appreciate any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database design help
A lot depends upon the sophistication of the program you write to manage this. I doubt there is any way to create something so sophisticated with just SQL. My first thought would be to use three tables. Make sure every user has a unique use ID. The users' passwords would be stored in the table of users (I'm assuming that the passwords are unique to users, rather than groups.) user_id autoincrement user_name user_pass User other information Each group would also have a unique group id. The table of groups would only contain three fields: group_id autoincrement owner's user id group_name This lets you find each user's owned groups. The name field is so that a user can readily see which group is which in a human-readable way. Then you want a table of group members, again with only two fields: group_id member_id Now to find a user's groups, you look for the user_id in the group table. To find its members, you look in the group member table. You can also work backwards to find all of the groups that a user belongs to by starting from the other direction. The password checking for managing a user's groups would be at the application level. You'd have one record Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: ppywriw [mailto:[EMAIL PROTECTED] Sent: Saturday, December 16, 2006 11:54 AM To: mysql@lists.mysql.com Subject: database design help Hiya, Real easy quick question. I need to design a database which holds users with email, name and some other details. I also want each user to be able to create one or more groups of users, owned by themselves. What would be the best design approach? So far i have a table for the users which stores their personal details, but i dont know where to go from here to create the groups? Create a new table for every group? The group would just contain a list of the users emails in that group. Or would i create a new table for the groups and attach a password field on it so only the user that created it could access it? A very newbie question i know, but i am one, i'll admit it. Any help would be apprectiated. Thanks John -- View this message in context: http://www.nabble.com/database-design-help-tf2832533.html#a7908028 Sent from the MySQL - General mailing list archive at Nabble.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 help
Hiya, Real easy quick question. I need to design a database which holds users with email, name and some other details. I also want each user to be able to create one or more groups of users, owned by themselves. What would be the best design approach? So far i have a table for the users which stores their personal details, but i dont know where to go from here to create the groups? Create a new table for every group? The group would just contain a list of the users emails in that group. Or would i create a new table for the groups and attach a password field on it so only the user that created it could access it? A very newbie question i know, but i am one, i'll admit it. Any help would be apprectiated. Thanks John -- View this message in context: http://www.nabble.com/database-design-help-tf2832533.html#a7908028 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design help
At 12:54 PM 12/16/2006, you wrote: Hiya, Real easy quick question. I need to design a database which holds users with email, name and some other details. I also want each user to be able to create one or more groups of users, owned by themselves. What would be the best design approach? So far i have a table for the users which stores their personal details, but i dont know where to go from here to create the groups? Create a new table for every group? The group would just contain a list of the users emails in that group. Or would i create a new table for the groups and attach a password field on it so only the user that created it could access it? A very newbie question i know, but i am one, i'll admit it. Any help would be apprectiated. Thanks John -- View this message in context: http://www.nabble.com/database-design-help-tf2832533.html#a7908028 Sent from the MySQL - General mailing list archive at Nabble.com. Presumably users is something like this: Members table ID - numeric, auto-generated BelongsTo - group id Firstname Surname email phone etc. But that's not right - A User can belong to only one group, most probably want to belong to many - so remove the BelongsTo field and let a refernce to the ID field of the membertable do the work GroupMembers table ID - numeric, autogenerated Group_ID - numeric, foreign key Member_ID - numeric, foreign key - refers to ID field in Members table and of course a Groups table, ID - numeric, autogenerated Managed_By - foreign key, refers to ID field in Members table Name other pertinent stuff There you go - three tables able to hold unlimited combinations of groups and members and you will never have a many to many problem. Cheers - Miles other info . -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.21/589 - Release Date: 12/15/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multilanguage database design issue
Hello, I'm not very experienced with MySql and I'm building a website that deal with user profiles. User profiles involves lists of preferences to choose from (for example, your contry, your profession, etc.), so the user chooses from a drop-down lists. The most natural solution for this is to define the related fields sometimes with an ENUM statement, sometimes with a SET statement. And now, the problem comes... the problem is that my site will be available in several languages, so it will have several versions, for example, www.englishversion.com, www.spanishversion.com, etc. Obviously I will not create a different database for each site with the same data in different languages. I could define many fields in the same table, suppose in my table Profile, I would put fields like country_french_enum, country_english_enum and so on. Each one would contain a list of all countries in french, in english, etc. But I feel it a little cumbersome and difficult to work with. Another solution whould consist to not define enums or sets at all. Instead I could just define tha country field as varchar in the Profile table and create a new table with static data, with the following fields or such: table name: country_names table fields: (country_id, country_name_en, country_name_fr, country_name_es, country_name_it, ...) So each time a form with a country drop-down list is loaded, it would load the country names from country_name table according to the language of the site. And when form is submitted, the choosen value would be validated and inserted in the country varchar field of profile table. This gives me the advantage of take my profile table simpler and faster. But the inconvenient is that now, I have much more field validations to do, I must validate each submitted choice, because a varchar field doesn't limit or delimit allowed values as ENUM or SET do. If I get speed with the database, I actually loses speed with the website or server load. If at one side I simplify things, I complicate them in another. So, I wrote this message to get some help and knowing more how these kind of issues are solved in real-world situations. Internet has lots of multilanguages database driven websites with similar data issues. Surely some standard solution is applied. Do you know any better solution than these ones I described? If not, cound you suggest me which is the best of the two? Thanks, I would appreciate a lot your help. Alphonse - Want to start your own business? Learn how on Yahoo! Small Business.
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 help
Hello, we currently have a small database setup for affilates and visitor/leads. I believe we have a one to many application, one affiliate can have several visitor/leads but each visitor can only be assigned to one affiliate. What I need to know if this the best design for this setup. Basically a visitor fills out a form, and is assigned to one affiliate. So I was wondering is it better to create a joining table between the `affiliates` table and the `visitors` table or will this design be efficent as it is. Below are the 2 tables in question CREATE TABLE IF NOT EXISTS affiliates ( affid int(6) unsigned NOT NULL auto_increment, affiliate_id int(10) unsigned NOT NULL default '', affiliate_email varchar(60) NOT NULL default '', PRIMARY KEY (affid), KEY affiliate_id (affiliate_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS visitors ( visitorid int(6) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', phone varchar(20) NOT NULL default '', email varchar(60) NOT NULL default '', state char(2) NOT NULL default '', ip varchar(20) NOT NULL default '', dtime datetime NOT NULL default '-00-00 00:00:00', exported varchar(10) default NULL, affid int(6) unsigned NOT NULL default '0', PRIMARY KEY (visitorid), KEY email (email), KEY affid (affid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; TIA, -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
- Original Message - From: Mike Blezien [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Friday, February 17, 2006 7:49 AM Subject: Database design help Hello, we currently have a small database setup for affilates and visitor/leads. I believe we have a one to many application, one affiliate can have several visitor/leads but each visitor can only be assigned to one affiliate. What I need to know if this the best design for this setup. Basically a visitor fills out a form, and is assigned to one affiliate. So I was wondering is it better to create a joining table between the `affiliates` table and the `visitors` table or will this design be efficent as it is. Below are the 2 tables in question CREATE TABLE IF NOT EXISTS affiliates ( affid int(6) unsigned NOT NULL auto_increment, affiliate_id int(10) unsigned NOT NULL default '', affiliate_email varchar(60) NOT NULL default '', PRIMARY KEY (affid), KEY affiliate_id (affiliate_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS visitors ( visitorid int(6) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', phone varchar(20) NOT NULL default '', email varchar(60) NOT NULL default '', state char(2) NOT NULL default '', ip varchar(20) NOT NULL default '', dtime datetime NOT NULL default '-00-00 00:00:00', exported varchar(10) default NULL, affid int(6) unsigned NOT NULL default '0', PRIMARY KEY (visitorid), KEY email (email), KEY affid (affid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; When you say joining table, I assume you mean an intersection table or association table, which are the more common terms that describe something used to break a many-to-many relationship into two one-to-many relationships. I've never heard it described as a joining table but I _think_ we're talking about the same thing In any case, I don't think you need anything but the two tables you have here. If there is only ever going to be a single affiliate assigned to a given visitor, then this is a one-to-many relationship and there is no need for an additional table. However, I would suggest one small amendment to your visitors table. Add the clause: FOREIGN KEY (affid) references affiliates(affid) on delete INSERT A DELETE RULE HERE This will ensure that you never add an affid other than a value found in the Affiliates table to the affid column of the visitors table. It will also ensure the proper behaviour when deletes take place in the affiliates table. For example, if you use ON DELETE CASCADE as your delete rule, if one of the affliates is deleted from the affiliates table, all of the rows with his ID will also be deleted from the visitors table. If you use ON DELETE RESTRICT, you will not be able to delete an affiliate from the affiliates table unless all of the Visitors rows with his ID have had their affid changed to that of some other affiliate. If you use on DELETE SET NULL, you can freely delete affiliates even if they have rows in the Visitors table; the Visitors rows will just have their affids set to null, which effectively means that those Visitors have no assigned affiliate. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.10/263 - Release Date: 16/02/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
OK, I think I got it now. Thanks for the additional info, that helps alot. Rhino wrote: - Original Message - From: Mike Blezien [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Friday, February 17, 2006 7:49 AM Subject: Database design help Hello, we currently have a small database setup for affilates and visitor/leads. I believe we have a one to many application, one affiliate can have several visitor/leads but each visitor can only be assigned to one affiliate. What I need to know if this the best design for this setup. Basically a visitor fills out a form, and is assigned to one affiliate. So I was wondering is it better to create a joining table between the `affiliates` table and the `visitors` table or will this design be efficent as it is. Below are the 2 tables in question CREATE TABLE IF NOT EXISTS affiliates ( affid int(6) unsigned NOT NULL auto_increment, affiliate_id int(10) unsigned NOT NULL default '', affiliate_email varchar(60) NOT NULL default '', PRIMARY KEY (affid), KEY affiliate_id (affiliate_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS visitors ( visitorid int(6) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', phone varchar(20) NOT NULL default '', email varchar(60) NOT NULL default '', state char(2) NOT NULL default '', ip varchar(20) NOT NULL default '', dtime datetime NOT NULL default '-00-00 00:00:00', exported varchar(10) default NULL, affid int(6) unsigned NOT NULL default '0', PRIMARY KEY (visitorid), KEY email (email), KEY affid (affid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; When you say joining table, I assume you mean an intersection table or association table, which are the more common terms that describe something used to break a many-to-many relationship into two one-to-many relationships. I've never heard it described as a joining table but I _think_ we're talking about the same thing In any case, I don't think you need anything but the two tables you have here. If there is only ever going to be a single affiliate assigned to a given visitor, then this is a one-to-many relationship and there is no need for an additional table. However, I would suggest one small amendment to your visitors table. Add the clause: FOREIGN KEY (affid) references affiliates(affid) on delete INSERT A DELETE RULE HERE This will ensure that you never add an affid other than a value found in the Affiliates table to the affid column of the visitors table. It will also ensure the proper behaviour when deletes take place in the affiliates table. For example, if you use ON DELETE CASCADE as your delete rule, if one of the affliates is deleted from the affiliates table, all of the rows with his ID will also be deleted from the visitors table. If you use ON DELETE RESTRICT, you will not be able to delete an affiliate from the affiliates table unless all of the Visitors rows with his ID have had their affid changed to that of some other affiliate. If you use on DELETE SET NULL, you can freely delete affiliates even if they have rows in the Visitors table; the Visitors rows will just have their affids set to null, which effectively means that those Visitors have no assigned affiliate. -- Rhino -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Visual database design system
I am looking for a tool to integrate with mysql...I have tried DBDesigner and would like to get my hands on software that is equivalent or better than DBDesigner...any suggestions? FYI: I have had some problems with importing, printing etc with DBDesigner... Thanks in advance...
RE: Visual database design system
Hello, Might want to check out ERStudio from Embarcadero Technologies. http://www.embarcadero.com/products/erstudio/ Jimmy Guerrero, Senior Product Manager MySQL Inc, www.mysql.com Houston, TX USA -Original Message- From: Adi [mailto:[EMAIL PROTECTED] Sent: Friday, February 03, 2006 9:53 AM To: mysql@lists.mysql.com Subject: Visual database design system I am looking for a tool to integrate with mysql...I have tried DBDesigner and would like to get my hands on software that is equivalent or better than DBDesigner...any suggestions? FYI: I have had some problems with importing, printing etc with DBDesigner... Thanks in advance... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual database design system
Adi, We use Dezign from Datanamic. Not free, not expensive either. PB - Adi wrote: I am looking for a tool to integrate with mysql...I have tried DBDesigner and would like to get my hands on software that is equivalent or better than DBDesigner...any suggestions? FYI: I have had some problems with importing, printing etc with DBDesigner... Thanks in advance... No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 2/3/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 2/3/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual database design system
Looked good, but does not suport MySQL 5 :( - cost is not really an issue On 2/3/06, Peter Brawley [EMAIL PROTECTED] wrote: Adi, We use Dezign from Datanamic. Not free, not expensive either. PB - Adi wrote: I am looking for a tool to integrate with mysql...I have tried DBDesigner and would like to get my hands on software that is equivalent or better than DBDesigner...any suggestions? FYI: I have had some problems with importing, printing etc with DBDesigner... Thanks in advance... -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 2/3/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 2/3/2006 -- Take care... Adam
Re: Visual database design system
Hey Jimmy...when I reverse engineer a DB, the software does not seem to make the relationship connections between the tables...all the keys are listed, but does not illustrate them on the diagram...any tips? Cant find anything in their soft help section... Thanks... On 2/3/06, Jimmy Guerrero [EMAIL PROTECTED] wrote: Hello, Might want to check out ERStudio from Embarcadero Technologies. http://www.embarcadero.com/products/erstudio/ Jimmy Guerrero, Senior Product Manager MySQL Inc, www.mysql.com Houston, TX USA -Original Message- From: Adi [mailto:[EMAIL PROTECTED] Sent: Friday, February 03, 2006 9:53 AM To: mysql@lists.mysql.com Subject: Visual database design system I am looking for a tool to integrate with mysql...I have tried DBDesigner and would like to get my hands on software that is equivalent or better than DBDesigner...any suggestions? FYI: I have had some problems with importing, printing etc with DBDesigner... Thanks in advance... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Take care... Adam
Re: Visual database design system
At 10:52 AM -0500 2/3/06, Adi wrote: I am looking for a tool to integrate with mysql...I have tried DBDesigner and would like to get my hands on software that is equivalent or better than DBDesigner...any suggestions? FYI: I have had some problems with importing, printing etc with DBDesigner... Thanks in advance... I've used Artiso Visual Case - http://www.visualcase.com/ - some. Java-based, still a bit rough here and there, but they have a free 30-day trial. Academic price (what I paid) much less expensive than standard license. I haven't tried it with MySQL 5 yet. If I recall clearly, it is similar in scope to Datanamic's DeZign - http://www.datanamic.com/ - which is (or was) Windows only. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20 // start off with blue boxes that are 20 in size 1 | 2006-02-01 | red | NULL // boxes are now red but same size 1 | 2006-03-01 | NULL | 30 // boxes are still red but are now 30 in size Or I could break off each field that changes into it's own table. Any recommendations? Thanks again. Ian At 12:35 AM 1/19/2006 +, Marco Neves wrote: Ian, I'ld like to help you, but a more specific db design would depend on more specific description on your application needs. What I can say is that you need to adapt your database to your reality. What I got til now is that you need a product table, where you can store your basic information on products. You say you have other information, but I could understand several things. 1- That other information is related to the product, to the transaction, to both, to stocks? for example, color or size is relevant to determine stocks and is related to the product, and so is relevant to the transactions also. The sale rep is relevant to transaction, but not to the product. sales rep comission is relevante to the sales rep, but not to the transaction nor the product. My point is, a database design can be a complex task, and the hability an application will have to provide solutions to the real world depends, before anyother thing in that database design. The is the point where almost all analisys most be done, and almost no programming (i think). mpneves On Wednesday 18 January 2006 22:55, you wrote: Thanks Ed. That's another good idea. The consensus I'm getting is to create one table that stores unchanging data about the product and another that stores transaction details. The problem I'm still having is how to efficiently handle more than one changing value. As an example, let's say I want to keep track of not only the quantity of a product but who the sales rep for that product is. While the quantity would change much more frequently than the sales rep I could put both in the same transaction table, but then I'll end up with duplicated data. For example, date | product_id | quantity | rep 2006-01-01 | 1 | 100 | rep 1 2006-02-01 | 1 | 98 | rep 1 2006-03-01 | 1 | 98 | rep 2 2006-04-01 | 1 | 50 | rep 2 Alternatively, I could create one table for the quantity and another for the sales rep. date | product_id | quantity 2006-01-01 | 1 | 100 2006-02-01 | 1 | 98 2006-04-01 | 1 | 50 date | product_id | rep 2006-01-01 | 1 | rep 1 2006-03-01 | 1 | rep 2 This seems to be the cleanest solution, other than requiring a table for every field that I want to track. Ian At 02:36 PM 1/18/2006 -0800, Ed Reed wrote: I built my inventory system like this, I have a products table that contains all the information specific to each part, less the quantity, i.e. Part Number, Description, Vendor, Color, Weight, SKU number, etc... Then I have another table that is my Inventory Tranactions Log that is just the following Date, ProductID, Qty, TypeOfTranacstion, Comment The inventory for each part may adjust daily or not. When parts are removed/sold the transaction log gets a record for that product and the number of parts that were sold and the type of transaction that occurred. When parts are received another transaction is entered for that part with the quantity received and the type of transaction that occurred. When we close the store and want to take a full inventory we first run a report that get the sums of all the transactions for each product and that tells us what should be on the shelf according to the database. Then we verify or adjust the qty for each product on the shelf by adding a record to the transaction log indicating the quantity and the type of transaction that occurred. When we want to see the values in the inventory its a very simple report to get the sums for each product. - Hope that helps. Ian Klassen [EMAIL PROTECTED] 1/18/06 10:09:55 AM Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also
Re: Database design help
Ian, If I'm not mistaken, you started this conversation yesterday. I've been watching the back-and-forth haphazardly and not really absorbing the full details so forgive me if someone has already asked this and you've answered it. My concern, in hearing you state your problem, is that some of the stuff you want to track just doesn't seem that important or, to put it another way, they just don't seem like the kinds of things that a business will really care that much about. For instance, this note mentions that the size or colour of a box has changed and you want to track that. Frankly, I'm having trouble believing that your management really _needs_ to track that kind of micro-change. Why would they care? Surely their major concerns must be things like sales of goods, profits, and inventories. What difference does the colour of the box make? Do you sell more widgets when they are in blue boxes than when they are in green boxes? Now, at some level, the packaging probably _does_ matter; I'm sure packaging experts will be able to trot out stories about how sales of widgets increased 14% when the box was changed in such-and-such a way. But do _you_ or your company really care about this enough to track the details about the packaging for every single item you stock? Or are you doing a detailed study to try to prove that the packaging really does make a difference of so many percent in sales? Otherwise, I'm at a loss to understand why you'd track that much detail. I caught glimspses of other requirements in the other notes that had comparable requirements; some of them struck me as things that were just not typically tracked in computer systems. I'm not saying you couldn't make a case for any of these requirements; maybe they are all essential for your project. But is it possible that you've taken a wouldn't it be nice if we could track XXX? remark that someone made and turned it into a do-or-die requirement? Is is possible that some of these requirements just aren't that important and could be omitted with no important loss of functionality? If you give this due consideration, you may find that a lot of your problem evaporates and the rest gets simpler to handle. Just a general observation made by a disinterested third party; ignore it if you like :-) Rhino - Original Message - From: Ian Klassen [EMAIL PROTECTED] To: Marco Neves [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, January 20, 2006 3:49 PM Subject: Re: Database design help Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20 // start off with blue boxes that are 20 in size 1 | 2006-02-01 | red | NULL // boxes are now red but same size 1 | 2006-03-01 | NULL | 30 // boxes are still red but are now 30 in size Or I could break off each field that changes into it's own table. Any recommendations? Thanks again. Ian At 12:35 AM 1/19/2006 +, Marco Neves wrote: Ian, I'ld like to help you, but a more specific db design would depend on more specific description on your application needs. What I can say is that you need to adapt your database to your reality. What I got til now is that you need a product table, where you can store your basic information on products. You say you have other information, but I could understand several things. 1- That other information is related to the product, to the transaction, to both, to stocks? for example, color or size is relevant to determine stocks and is related to the product, and so is relevant to the transactions also. The sale rep is relevant to transaction, but not to the product. sales rep comission is relevante to the sales rep, but not to the transaction nor the product. My point is, a database design can be a complex task, and the hability an application will have to provide solutions to the real world depends, before anyother thing in that database design. The is the point where almost all analisys most be done, and almost no programming (i think). mpneves On Wednesday 18 January 2006 22:55, you wrote: Thanks Ed. That's another good idea. The consensus I'm getting is to create one table that stores unchanging data about the product and another that stores transaction details. The problem I'm
Re: Database design help
Rhino, I appreciate your comments. This wasn't meant to be a real world example. My actual application keeps track of changing data in a gas network. I wanted to simplify the problem to help in finding an answer to my dilemmas. Ian At 04:45 PM 1/20/2006 -0500, Rhino wrote: Ian, If I'm not mistaken, you started this conversation yesterday. I've been watching the back-and-forth haphazardly and not really absorbing the full details so forgive me if someone has already asked this and you've answered it. My concern, in hearing you state your problem, is that some of the stuff you want to track just doesn't seem that important or, to put it another way, they just don't seem like the kinds of things that a business will really care that much about. For instance, this note mentions that the size or colour of a box has changed and you want to track that. Frankly, I'm having trouble believing that your management really _needs_ to track that kind of micro-change. Why would they care? Surely their major concerns must be things like sales of goods, profits, and inventories. What difference does the colour of the box make? Do you sell more widgets when they are in blue boxes than when they are in green boxes? Now, at some level, the packaging probably _does_ matter; I'm sure packaging experts will be able to trot out stories about how sales of widgets increased 14% when the box was changed in such-and-such a way. But do _you_ or your company really care about this enough to track the details about the packaging for every single item you stock? Or are you doing a detailed study to try to prove that the packaging really does make a difference of so many percent in sales? Otherwise, I'm at a loss to understand why you'd track that much detail. I caught glimspses of other requirements in the other notes that had comparable requirements; some of them struck me as things that were just not typically tracked in computer systems. I'm not saying you couldn't make a case for any of these requirements; maybe they are all essential for your project. But is it possible that you've taken a wouldn't it be nice if we could track XXX? remark that someone made and turned it into a do-or-die requirement? Is is possible that some of these requirements just aren't that important and could be omitted with no important loss of functionality? If you give this due consideration, you may find that a lot of your problem evaporates and the rest gets simpler to handle. Just a general observation made by a disinterested third party; ignore it if you like :-) Rhino - Original Message - From: Ian Klassen [EMAIL PROTECTED] To: Marco Neves [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, January 20, 2006 3:49 PM Subject: Re: Database design help Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20 // start off with blue boxes that are 20 in size 1 | 2006-02-01 | red | NULL // boxes are now red but same size 1 | 2006-03-01 | NULL | 30 // boxes are still red but are now 30 in size Or I could break off each field that changes into it's own table. Any recommendations? Thanks again. Ian At 12:35 AM 1/19/2006 +, Marco Neves wrote: Ian, I'ld like to help you, but a more specific db design would depend on more specific description on your application needs. What I can say is that you need to adapt your database to your reality. What I got til now is that you need a product table, where you can store your basic information on products. You say you have other information, but I could understand several things. 1- That other information is related to the product, to the transaction, to both, to stocks? for example, color or size is relevant to determine stocks and is related to the product, and so is relevant to the transactions also. The sale rep is relevant to transaction, but not to the product. sales rep comission is relevante to the sales rep, but not to the transaction nor the product. My point is, a database design can be a complex task, and the hability an application will have to provide solutions to the real world depends, before anyother thing in that database design. The is the point where almost all analisys most be done, and almost
Re: Database design help
Perhaps you could use database triggers to keep track of these changes. I also think there's a way you could make the change tracking a little neater if you don't mind sacrificing some SQL functionality and storing everything as text. If you create a table product_changes, with 4 columns - id, change_date, change_type, and current_value, you could add one or more entries each time or more aspects of your product change. If your salesrep changes, set your change_type = salesrep If your color changes, set your change_type = color. If your size changes, set your change_type = size. If nothing changes, then add no entry. You could have the database track this for you with triggers, so you wouldn't even have to do . I haven't used triggers with MySQL so I'm sure my syntax is off but here's a rough outline of what you could do create trigger ProductUpdateTrig on Product for update as begin if (newsalesrep != oldsalesrep) insert into product_changes values (now(), 'salesrep', oldsalesrep) if (newcolor != oldcolor) insert into product_changes values (now(), 'color', oldcolor) if (newsize != oldsize) insert into product_changes values (now(), 'size', oldsize) end If you wanted to keep track of additions and deletions you could create similar insert and delete triggers. Note that you would likely have to store the values as text even if they were originally numeric or DATETIME, in order to be able to use a simple table to keep track of all the different kinds of changes. Hope this helps, Dan At 4:45 PM -0500 1/20/06, Rhino wrote: Ian, If I'm not mistaken, you started this conversation yesterday. I've been watching the back-and-forth haphazardly and not really absorbing the full details so forgive me if someone has already asked this and you've answered it. My concern, in hearing you state your problem, is that some of the stuff you want to track just doesn't seem that important or, to put it another way, they just don't seem like the kinds of things that a business will really care that much about. For instance, this note mentions that the size or colour of a box has changed and you want to track that. Frankly, I'm having trouble believing that your management really _needs_ to track that kind of micro-change. Why would they care? Surely their major concerns must be things like sales of goods, profits, and inventories. What difference does the colour of the box make? Do you sell more widgets when they are in blue boxes than when they are in green boxes? Now, at some level, the packaging probably _does_ matter; I'm sure packaging experts will be able to trot out stories about how sales of widgets increased 14% when the box was changed in such-and-such a way. But do _you_ or your company really care about this enough to track the details about the packaging for every single item you stock? Or are you doing a detailed study to try to prove that the packaging really does make a difference of so many percent in sales? Otherwise, I'm at a loss to understand why you'd track that much detail. I caught glimspses of other requirements in the other notes that had comparable requirements; some of them struck me as things that were just not typically tracked in computer systems. I'm not saying you couldn't make a case for any of these requirements; maybe they are all essential for your project. But is it possible that you've taken a wouldn't it be nice if we could track XXX? remark that someone made and turned it into a do-or-die requirement? Is is possible that some of these requirements just aren't that important and could be omitted with no important loss of functionality? If you give this due consideration, you may find that a lot of your problem evaporates and the rest gets simpler to handle. Just a general observation made by a disinterested third party; ignore it if you like :-) Rhino - Original Message - From: Ian Klassen [EMAIL PROTECTED] To: Marco Neves [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, January 20, 2006 3:49 PM Subject: Re: Database design help Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20 // start off with blue boxes that are 20 in size 1 | 2006-02-01 | red | NULL // boxes are now red but same size 1 | 2006-03-01 | NULL | 30 // boxes are still red but are now 30
Re: Database design help
Hi Dan, It would be a pretty large table of changes, but this solution would work, if as you say, I don't mind making those sacrifices. Something to think about. Thanks. Ian At 03:59 PM 1/20/2006 -0600, Dan Buettner wrote: Perhaps you could use database triggers to keep track of these changes. I also think there's a way you could make the change tracking a little neater if you don't mind sacrificing some SQL functionality and storing everything as text. If you create a table product_changes, with 4 columns - id, change_date, change_type, and current_value, you could add one or more entries each time or more aspects of your product change. If your salesrep changes, set your change_type = salesrep If your color changes, set your change_type = color. If your size changes, set your change_type = size. If nothing changes, then add no entry. You could have the database track this for you with triggers, so you wouldn't even have to do . I haven't used triggers with MySQL so I'm sure my syntax is off but here's a rough outline of what you could do create trigger ProductUpdateTrig on Product for update as begin if (newsalesrep != oldsalesrep) insert into product_changes values (now(), 'salesrep', oldsalesrep) if (newcolor != oldcolor) insert into product_changes values (now(), 'color', oldcolor) if (newsize != oldsize) insert into product_changes values (now(), 'size', oldsize) end If you wanted to keep track of additions and deletions you could create similar insert and delete triggers. Note that you would likely have to store the values as text even if they were originally numeric or DATETIME, in order to be able to use a simple table to keep track of all the different kinds of changes. Hope this helps, Dan At 4:45 PM -0500 1/20/06, Rhino wrote: Ian, If I'm not mistaken, you started this conversation yesterday. I've been watching the back-and-forth haphazardly and not really absorbing the full details so forgive me if someone has already asked this and you've answered it. My concern, in hearing you state your problem, is that some of the stuff you want to track just doesn't seem that important or, to put it another way, they just don't seem like the kinds of things that a business will really care that much about. For instance, this note mentions that the size or colour of a box has changed and you want to track that. Frankly, I'm having trouble believing that your management really _needs_ to track that kind of micro-change. Why would they care? Surely their major concerns must be things like sales of goods, profits, and inventories. What difference does the colour of the box make? Do you sell more widgets when they are in blue boxes than when they are in green boxes? Now, at some level, the packaging probably _does_ matter; I'm sure packaging experts will be able to trot out stories about how sales of widgets increased 14% when the box was changed in such-and-such a way. But do _you_ or your company really care about this enough to track the details about the packaging for every single item you stock? Or are you doing a detailed study to try to prove that the packaging really does make a difference of so many percent in sales? Otherwise, I'm at a loss to understand why you'd track that much detail. I caught glimspses of other requirements in the other notes that had comparable requirements; some of them struck me as things that were just not typically tracked in computer systems. I'm not saying you couldn't make a case for any of these requirements; maybe they are all essential for your project. But is it possible that you've taken a wouldn't it be nice if we could track XXX? remark that someone made and turned it into a do-or-die requirement? Is is possible that some of these requirements just aren't that important and could be omitted with no important loss of functionality? If you give this due consideration, you may find that a lot of your problem evaporates and the rest gets simpler to handle. Just a general observation made by a disinterested third party; ignore it if you like :-) Rhino - Original Message - From: Ian Klassen [EMAIL PROTECTED] To: Marco Neves [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, January 20, 2006 3:49 PM Subject: Re: Database design help Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20
Database design help
Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory | product a | product b | product c 2006-01-02 | 100 | 50| 25 2006-01-03 | NULL | 45| NULL 2006-01-05 | 78| NULL | 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date | end_date | product a | pa_up | product b | pb_up | product c | pc_up 2006-01-02 | 2006-01-03| 100 | TRUE | 50 | TRUE | 25| TRUE 2006-01-03 | 2006-01-05| 100 | FALSE | 45| TRUE | 25| FALSE 2006-01-05 | 2006-01-05| 78| TRUE | 45 | FALSE | 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
Hi, Why don't you create two table: * a product table, with the product discriptions, and other product related info (call it prod): |ID|NAME|SOME|OTHER|FIELDS| |1|ProdA|..|..|..| |2|ProdB|..|..|..| * a stock movements table, with moviments by product (call it pro_move): |ID|PROD__ID|DAY|MOV|DESCRIPT| |1|1|2006-01-01|10|Inventory at Jan 1st for Prod A| |2|2|2006-01-01|25|Inventory at Jan 1st for Prod B| |3|1|2006-01-02|-5|Selled 5 units of A at Jan 2nd| Then to know the inventary to up-to-date of every product you can do: SELECT p.id,p.name,sum(pm.mov),max(day) FROM prod p LEFT JOIN prod_move pm ON p.id=pm.prod__id GROUP by p.id; If you think your product or move table will grow too big you can add a stock column to the prod table and update that field when you add a movement to your prod_move table, and verify that value from time to time (and if possible just add movement in transaction, with both tables suporting them - InnoDB ou DBD). This is the way I would do it. What you think? mpneves On Wednesday 18 January 2006 18:09, Ian Klassen wrote: Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory | product a | product b | product c 2006-01-02| 100 | 50| 25 2006-01-03| NULL | 45| NULL 2006-01-05| 78| NULL | 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date | product a | pa_up | product b | pb_up | product c | pc_up 2006-01-02| 2006-01-03| 100 | TRUE | 50 | TRUE | 25| TRUE 2006-01-03| 2006-01-05| 100 | FALSE | 45| TRUE | 25| FALSE 2006-01-05| 2006-01-05| 78| TRUE | 45 | FALSE | 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
Ian Klassen [EMAIL PROTECTED] wrote on 01/18/2006 01:09:55 PM: Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory | product a | product b | product c 2006-01-02 | 100 | 50 | 25 2006-01-03 | NULL | 45 | NULL 2006-01-05 | 78 | NULL | 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date | end_date | product a | pa_up | product b | pb_up | product c | pc_up 2006-01-02 | 2006-01-03 | 100 | TRUE | 50 | TRUE | 25 | TRUE 2006-01-03 | 2006-01-05 | 100 | FALSE | 45 | TRUE | 25 | FALSE 2006-01-05 | 2006-01-05 | 78 | TRUE | 45 | FALSE | 22 | TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian Something you didn't think of: CREATE TABLE physical_inventory ( date_of_inventory datetime, product_id int unsigned, quantity int, PRIMARY KEY(product_id, date_of_inventory) ) Then determinining the current inventory is a simple two-step process (also known as finding the groupwise maximum): A) find the latest date_of_inventory for each product: CREATE TABLE tmpInv(KEY(product_id, date_of_inventory)) SELECT product_id, max(date_of_inventory) date_of_inventory FROM inventory GROUP BY product_id; B) rejoin to your original table to get the quantity SELECT ti.product_id, ti.date_of_inventory, i.quantity FROM tmpInv ti INNER JOIN inventory i ON ti.product_ID = i.product_id AND ti.date_of_inventory = i.date_of_inventory; DROP TABLE tmpInv; With this design, you won't have an inventory table of several hundred columns and you won't need to change your database design every time a product is added or removed from inventory. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Database design help
At 06:27 PM 1/18/2006 +, Marco Neves wrote: Hi, Why don't you create two table: * a product table, with the product discriptions, and other product related info (call it prod): |ID|NAME|SOME|OTHER|FIELDS| |1|ProdA|..|..|..| |2|ProdB|..|..|..| * a stock movements table, with moviments by product (call it pro_move): |ID|PROD__ID|DAY|MOV|DESCRIPT| |1|1|2006-01-01|10|Inventory at Jan 1st for Prod A| |2|2|2006-01-01|25|Inventory at Jan 1st for Prod B| |3|1|2006-01-02|-5|Selled 5 units of A at Jan 2nd| Then to know the inventary to up-to-date of every product you can do: SELECT p.id,p.name,sum(pm.mov),max(day) FROM prod p LEFT JOIN prod_move pm ON p.id=pm.prod__id GROUP by p.id; If you think your product or move table will grow too big you can add a stock column to the prod table and update that field when you add a movement to your prod_move table, and verify that value from time to time (and if possible just add movement in transaction, with both tables suporting them - InnoDB ou DBD). This is the way I would do it. What you think? mpneves On Wednesday 18 January 2006 18:09, Ian Klassen wrote: Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory | product a | product b | product c 2006-01-02| 100 | 50| 25 2006-01-03| NULL | 45| NULL 2006-01-05| 78| NULL | 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date | product a | pa_up | product b | pb_up | product c | pc_up 2006-01-02| 2006-01-03| 100 | TRUE | 50| TRUE | 25| TRUE 2006-01-03| 2006-01-05| 100 | FALSE | 45| TRUE | 25| FALSE 2006-01-05| 2006-01-05| 78| TRUE | 45| FALSE | 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP Hi, The problem I run into then is that it's not just the inventory that changes. Each product has it's own set of custom fields that change with time. With this scenario, the stock movements table would require columns for each value that can change. Or I could create a table for each field that changes with time but that could get unwieldily very quickly. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
I built my inventory system like this, I have a products table that contains all the information specific to each part, less the quantity, i.e. Part Number, Description, Vendor, Color, Weight, SKU number, etc... Then I have another table that is my Inventory Tranactions Log that is just the following Date, ProductID, Qty, TypeOfTranacstion, Comment The inventory for each part may adjust daily or not. When parts are removed/sold the transaction log gets a record for that product and the number of parts that were sold and the type of transaction that occurred. When parts are received another transaction is entered for that part with the quantity received and the type of transaction that occurred. When we close the store and want to take a full inventory we first run a report that get the sums of all the transactions for each product and that tells us what should be on the shelf according to the database. Then we verify or adjust the qty for each product on the shelf by adding a record to the transaction log indicating the quantity and the type of transaction that occurred. When we want to see the values in the inventory its a very simple report to get the sums for each product. - Hope that helps. Ian Klassen [EMAIL PROTECTED] 1/18/06 10:09:55 AM Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory| product a| product b| product c 2006-01-02| 100| 50| 25 2006-01-03| NULL| 45| NULL 2006-01-05| 78| NULL| 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date| product a| pa_up| product b| pb_up| product c | pc_up 2006-01-02| 2006-01-03| 100| TRUE| 50| TRUE| 25 | TRUE 2006-01-03| 2006-01-05| 100| FALSE| 45| TRUE| 25| FALSE 2006-01-05| 2006-01-05| 78| TRUE| 45| FALSE| 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
Thanks Ed. That's another good idea. The consensus I'm getting is to create one table that stores unchanging data about the product and another that stores transaction details. The problem I'm still having is how to efficiently handle more than one changing value. As an example, let's say I want to keep track of not only the quantity of a product but who the sales rep for that product is. While the quantity would change much more frequently than the sales rep I could put both in the same transaction table, but then I'll end up with duplicated data. For example, date | product_id | quantity | rep 2006-01-01 | 1 | 100 | rep 1 2006-02-01 | 1 | 98 | rep 1 2006-03-01 | 1 | 98 | rep 2 2006-04-01 | 1 | 50 | rep 2 Alternatively, I could create one table for the quantity and another for the sales rep. date | product_id | quantity 2006-01-01 | 1 | 100 2006-02-01 | 1 | 98 2006-04-01 | 1 | 50 date | product_id | rep 2006-01-01 | 1 | rep 1 2006-03-01 | 1 | rep 2 This seems to be the cleanest solution, other than requiring a table for every field that I want to track. Ian At 02:36 PM 1/18/2006 -0800, Ed Reed wrote: I built my inventory system like this, I have a products table that contains all the information specific to each part, less the quantity, i.e. Part Number, Description, Vendor, Color, Weight, SKU number, etc... Then I have another table that is my Inventory Tranactions Log that is just the following Date, ProductID, Qty, TypeOfTranacstion, Comment The inventory for each part may adjust daily or not. When parts are removed/sold the transaction log gets a record for that product and the number of parts that were sold and the type of transaction that occurred. When parts are received another transaction is entered for that part with the quantity received and the type of transaction that occurred. When we close the store and want to take a full inventory we first run a report that get the sums of all the transactions for each product and that tells us what should be on the shelf according to the database. Then we verify or adjust the qty for each product on the shelf by adding a record to the transaction log indicating the quantity and the type of transaction that occurred. When we want to see the values in the inventory its a very simple report to get the sums for each product. - Hope that helps. Ian Klassen [EMAIL PROTECTED] 1/18/06 10:09:55 AM Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory| product a| product b| product c 2006-01-02| 100| 50| 25 2006-01-03| NULL| 45| NULL 2006-01-05| 78| NULL| 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date| product a| pa_up| product b| pb_up| product c | pc_up 2006-01-02| 2006-01-03| 100| TRUE| 50| TRUE| 25 | TRUE 2006-01-03| 2006-01-05| 100| FALSE| 45| TRUE| 25| FALSE 2006-01-05| 2006-01-05| 78| TRUE| 45| FALSE| 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- 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 help
Ian, I'ld like to help you, but a more specific db design would depend on more specific description on your application needs. What I can say is that you need to adapt your database to your reality. What I got til now is that you need a product table, where you can store your basic information on products. You say you have other information, but I could understand several things. 1- That other information is related to the product, to the transaction, to both, to stocks? for example, color or size is relevant to determine stocks and is related to the product, and so is relevant to the transactions also. The sale rep is relevant to transaction, but not to the product. sales rep comission is relevante to the sales rep, but not to the transaction nor the product. My point is, a database design can be a complex task, and the hability an application will have to provide solutions to the real world depends, before anyother thing in that database design. The is the point where almost all analisys most be done, and almost no programming (i think). mpneves On Wednesday 18 January 2006 22:55, you wrote: Thanks Ed. That's another good idea. The consensus I'm getting is to create one table that stores unchanging data about the product and another that stores transaction details. The problem I'm still having is how to efficiently handle more than one changing value. As an example, let's say I want to keep track of not only the quantity of a product but who the sales rep for that product is. While the quantity would change much more frequently than the sales rep I could put both in the same transaction table, but then I'll end up with duplicated data. For example, date | product_id | quantity | rep 2006-01-01 | 1 | 100 | rep 1 2006-02-01 | 1 | 98 | rep 1 2006-03-01 | 1 | 98 | rep 2 2006-04-01 | 1 | 50 | rep 2 Alternatively, I could create one table for the quantity and another for the sales rep. date | product_id | quantity 2006-01-01 | 1 | 100 2006-02-01 | 1 | 98 2006-04-01 | 1 | 50 date | product_id | rep 2006-01-01 | 1 | rep 1 2006-03-01 | 1 | rep 2 This seems to be the cleanest solution, other than requiring a table for every field that I want to track. Ian At 02:36 PM 1/18/2006 -0800, Ed Reed wrote: I built my inventory system like this, I have a products table that contains all the information specific to each part, less the quantity, i.e. Part Number, Description, Vendor, Color, Weight, SKU number, etc... Then I have another table that is my Inventory Tranactions Log that is just the following Date, ProductID, Qty, TypeOfTranacstion, Comment The inventory for each part may adjust daily or not. When parts are removed/sold the transaction log gets a record for that product and the number of parts that were sold and the type of transaction that occurred. When parts are received another transaction is entered for that part with the quantity received and the type of transaction that occurred. When we close the store and want to take a full inventory we first run a report that get the sums of all the transactions for each product and that tells us what should be on the shelf according to the database. Then we verify or adjust the qty for each product on the shelf by adding a record to the transaction log indicating the quantity and the type of transaction that occurred. When we want to see the values in the inventory its a very simple report to get the sums for each product. - Hope that helps. Ian Klassen [EMAIL PROTECTED] 1/18/06 10:09:55 AM Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory| product a| product b| product c 2006-01-02| 100| 50| 25 2006-01-03| NULL| 45| NULL 2006-01-05| 78| NULL| 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date| product a| pa_up| product b| pb_up| product c | pc_up 2006-01-02| 2006-01-03| 100| TRUE| 50| TRUE| 25 | TRUE 2006-01-03
MySQL database design documentation
Hi, I'm sure this is a stupid question, but I haven't been able to find it myself. Surely there must be a free PHP utility to web-administrate a MySQL database? I use CocoaMySQL (http://cocoamysql.sourceforge.net/) on my own Mac, but it isn't suitable for online databases. Can anyone lead me in the right direction? Thanks, Maurice van Peursem The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL database design documentation
Hello, Two admin tools to check out if you haven't already... PHP, you can try PHPMyAdmin - http://www.phpmyadmin.net/home_page/index.php Non-PHP, try MySQL's GPL MySQL Administrator - http://dev.mysql.com/downloads/administrator/index.html However, they too may not be suitable for remote admin depending on your setup and security needs. Thanks, Jimmy Guerrero, Senior Product Manager MySQL Inc, www.mysql.com Houston, TX USA Phone: (713) 636-9239 -Original Message- From: Maurice van Peursem [mailto:[EMAIL PROTECTED] Sent: Thursday, January 05, 2006 3:37 PM To: mysql@lists.mysql.com Subject: MySQL database design documentation Hi, I'm sure this is a stupid question, but I haven't been able to find it myself. Surely there must be a free PHP utility to web-administrate a MySQL database? I use CocoaMySQL (http://cocoamysql.sourceforge.net/) on my own Mac, but it isn't suitable for online databases. Can anyone lead me in the right direction? Thanks, Maurice van Peursem The Netherlands -- 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 - master table of countries and state/provinces
I'm trying to think of the ways I can accomplish having a master database of countries and states/provinces that I can reference in several other databases. I'd like to have a table of countries available for selection by the users in a web app, but I'm not sure of the best way to then allow them to pick the state/province in which they reside in that country. I've thought of a foreign key in the state table that references the parent country and do a state/province lookup off of that (SELECT state FROM tblStates JOIN ON tblState.countryID=WhatYouChoseAlready, or something similar - my syntax may not be correct). Is there another way that has been used by anyone with good success? Any suggestions welcome. And would ISO.org be the best place to find such country and province lists, or perhaps the Postal Service? Any past experience? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL database design documentation
Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? Thanks, Maurice van Peursem The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
- Original Message - From: Maurice van Peursem [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, November 27, 2005 6:33 PM Subject: MySQL database design documentation Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? For the most part, _any_ good database design book for _any_ decent relational database should do the job for you. That's because all (?) of the professional grade databases use the same SQL and the same normalization techniques to decide what columns belong in what tables and what primary and foreign keys should be used. Therefore, a good design book for DB2 or Oracle or Sybase would probably tell you almost exactly the same things as a good design book specifically intended for MySQL. You will still need to use the MySQL reference to help you with places where the MySQL syntax is slightly different than the syntax used by the other database but this really shouldn't happen too often. However, if you want a design book specifically written for MySQL, you may want to look at http://www.informit.com/articles/article.asp?p=30885rl=1. I should stress that I don't have this book, nor have I read it cover to cover. But the sample chapter on database design is pretty good, so, if the rest of the book is as good, you should come out okay. In fact, you may find that the sample chapter alone, which you can read online for free, may tell you everything you really want to know and save you the cost of the book. No guarantees on that but it's a starting point anyway. By the way, I have not seen any other MySQL Design books so there may be others that are better. The URL I've given you actually mentions some other books specifically for MySQL that may suit your personal learning style better. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
A couple of good links for databases. Database Design (quick and dirty, but gets the points across): http://www.geekgirls.com/menu_databases.htm - the from scratch side SQL: Basics: http://www.sqlcourse.com (you probably already know this stuff - but just in case. semi-Advanced: http://sqlcourse2.com (joins are specifically at http://sqlcourse2.com/joins.html). Maurice van Peursem wrote: Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? Thanks, Maurice van Peursem The Netherlands -- life is a game... so have fun. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
Relational Database Design Clearly Explained, Second Edition ISBN: 1558608206 The original edition was my first primer on relational databases. It was an excellent read. Ben Maurice van Peursem wrote: Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? Thanks, Maurice van Peursem The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design
Matthew Lenz wrote: anyone using openoffice:base to design mysql db's? back when I tried it earlier this year it wasn't able to define relationships which made it pretty much useless as a time saving tool. Hi Matt, Although it's slightly OT here, there is still a lot of development going on in Base. The most recent version I downloaded is a version 2 beta with internal version 1.9.130. It has lot's of improvements over previous releases, but it's still not the final release version. The best thing you can do is try it with a recent build (the 1.x series also had a recent update to 1.1.5 BTW) and submit an issue in the bug tracking system on the site. If you include version numbers of your OS, MySQL, etc. and detailed instructions on how to duplicate the problems you encountered you can help the development a bit further. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database design
anyone using openoffice:base to design mysql db's? back when I tried it earlier this year it wasn't able to define relationships which made it pretty much useless as a time saving tool. -Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
good database design
I need links about good database design information for high loaded web sites... regards, okan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
I need links about good database design information for high loaded web sites... A database design should start with the logical data-related requirements, not with performance related issues. IMO, of course. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. In times long since gone by (I am showing my age here) client side message response times were written into contracts. Design had to take into account performance issues. With very high loaded web-sites as in this case, a little time spent on lateral thinking can make a big difference and save costs in the long run, and keeps customers happy. Tim Hayes MYdbPAL - www.it-map.com -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 22 September 2005 09:02 To: mysql@lists.mysql.com Subject: Re: good database design I need links about good database design information for high loaded web sites... A database design should start with the logical data-related requirements, not with performance related issues. IMO, of course. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.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: good database design
Hi, Please reply to the list and not to me personally only. I want to explain my condition. I have a web site that habe 110onlne users at same time. But cpu usage is 2.00/2.00 (p4 3.0ghzHT) I think my database design is horrible because of this high cpu load. Did you do an analysis to come to this conclusion? Is it really MySQL that's hogging your CPU? Did you analyse what queries were bringing the server down? SO I want to learn something about good database design. Can you suggest any thing to me? Read a book, any book, that describes the normal forms. After that, analyse your queries and their plans and see if any indices are needed. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Hello Tim, I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. Given that the OP did not state that there were any issues with an existing website, logical requirements come first. Period. No discussion ;) In times long since gone by (I am showing my age here) client side message response times were written into contracts. Design had to take into account performance issues. With very high loaded web-sites as in this case, a little time spent on lateral thinking can make a big difference and save costs in the long run, and keeps customers happy. It also depends heavily on the tasks of the application. In any case, if this is a read/write application, I would still say that logical requirements should go first. If this is a read only application, do whatever you want. Given that - usually - data is pretty much the most important thing inside an application, it should be logically correct. Both you and me know that any denormalization or other performance tweaks can result into inconsistent data and should be avoided like the plague if possible. If this is the customers own server and everything is logical correct but there are some performance problems, I'd say: throw more hardware at it. Obviously, this makes sense --after-- tweaks to the database engine caching etc etc... Hardware is cheap(ish). If you can control it, do so. Denormalization is dangerous. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
sorry for wrong reply:( And Did you do an analysis to come to this conclusion? Is it really MySQL that's hogging your CPU? Did you analyse what queries were bringing the server down? The senteces above are my big problem. How can I be sure about the quesries making my server down Please, any link, any info, any word is important forme. I can't find the right start point. Thanks OKAN - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 22, 2005 11:25 AM Subject: Re: good database design Hi, Please reply to the list and not to me personally only. I want to explain my condition. I have a web site that habe 110onlne users at same time. But cpu usage is 2.00/2.00 (p4 3.0ghzHT) I think my database design is horrible because of this high cpu load. Did you do an analysis to come to this conclusion? Is it really MySQL that's hogging your CPU? Did you analyse what queries were bringing the server down? SO I want to learn something about good database design. Can you suggest any thing to me? Read a book, any book, that describes the normal forms. After that, analyse your queries and their plans and see if any indices are needed. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.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: good database design
Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
This is an interesting subject area. In a data warehousing environment, one tends to adopt table structures such as snowflake layouts which lead to improved performance. Createing a perfect normalised database design may well lead to performance issues. The more joins you have, by far the worse the performance. You may need to consider horizontal or vertical table splits. You may need to consider replicating certain data in child tables to avoid joins. I am not saying you do not need to carry out data analysis and gain a full and first hand understanding of the data structures. It is just that when it comes to online performance, sometimes you have to break the rules. -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: 22 September 2005 09:58 To: Ian Sales (DBA); [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: good database design My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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: good database design
Martijn Tonies wrote: Given that the OP did not state that there were any issues with an existing website, logical requirements come first. Period. No discussion ;) Logical requirements may come first, but may be overruled later by requirements caused by performance issues or system limitations. If your logic designed a large type of primary key, you may run into problems with InnoDB tables. The PK is stored with the data and other indexes refer to the PK (and not directly to the data as is the case with MyISAM). So a large PK will increase the table size (data + indexes) and may thus lead to performance issues when the database does not fit in memory anymore, or when the buffers,etc. hit the memory limits on your system. A very complex model may lead to queries with more than 31 JOINs, which is not possible with MySQL without modifying the source and recompiling it (and even then the limit seems to be 63). In any case, if this is a read/write application, I would still say that logical requirements should go first. If this is a read only application, do whatever you want. Logic may come first in the time line, but may be overruled by other requirements. Finding people who celebrate their birthday today (or this week) may become a very slow task if you only use a logical data field. Denormalisation by using extra fields for particular tasks is a completely logical solution in this case. If this is the customers own server and everything is logical correct but there are some performance problems, I'd say: throw more hardware at it. Obviously, this makes sense --after-- tweaks to the database engine caching etc etc... Hardware is cheap(ish). If you can control it, do so. Throwing hardware at it is not always a good solution. You know better than that. The customer better not find out that the application could very well run on the original server with a few tweaks as you call them, and that he appears to have lost a lot of money for new hardware and all the time needed to get the new server running in the configuration that you suggested... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Hi, Given that the OP did not state that there were any issues with an existing website, logical requirements come first. Period. No discussion ;) Logical requirements may come first, but may be overruled later by requirements caused by performance issues or system limitations. Which is what I said :-) If your logic designed a large type of primary key, you may run into problems with InnoDB tables. The PK is stored with the data and other indexes refer to the PK (and not directly to the data as is the case with MyISAM). So a large PK will increase the table size (data + indexes) and may thus lead to performance issues when the database does not fit in memory anymore, or when the buffers,etc. hit the memory limits on your system. A very complex model may lead to queries with more than 31 JOINs, which is not possible with MySQL without modifying the source and recompiling it (and even then the limit seems to be 63). Obviously, the MySQL guys should be bugged about this... In any case, if this is a read/write application, I would still say that logical requirements should go first. If this is a read only application, do whatever you want. Logic may come first in the time line, but may be overruled by other requirements. Finding people who celebrate their birthday today (or this week) may become a very slow task if you only use a logical data field. Denormalisation by using extra fields for particular tasks is a completely logical solution in this case. If this is the customers own server and everything is logical correct but there are some performance problems, I'd say: throw more hardware at it. Obviously, this makes sense --after-- tweaks to the database engine caching etc etc... Hardware is cheap(ish). If you can control it, do so. Throwing hardware at it is not always a good solution. You know better than that. The customer better not find out that the application could very well run on the original server with a few tweaks as you call them, and that he appears to have lost a lot of money for new hardware and all the time needed to get the new server running in the configuration that you suggested... Did you read my paragraph about throwing hardware? No offence, but I stated several times that the logical data requirements should come first, in design. After that, tweak the server, after that, if possible, throw more hardware at it. Now, if this doesn't cut it, you might get into denormalization or other things that make your application run faster... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
This is an interesting subject area. In a data warehousing environment, one tends to adopt table structures such as snowflake layouts which lead to improved performance. Createing a perfect normalised database design may well lead to performance issues. If this is the case, go bug the database vendors :-) ... they should give us systems that work properly ... The more joins you have, by far the worse the performance. You may That's a pretty bold statement... need to consider horizontal or vertical table splits. You may need to consider replicating certain data in child tables to avoid joins. I am not saying you do not need to carry out data analysis and gain a full and first hand understanding of the data structures. It is just that when it comes to online performance, sometimes you have to break the rules. But still: logical first, performance later... If at all. I once joined a team that had a running Oracle database and an application on top of it. We were having performance problems and there was the 2 seconds of max response time requirement in the contract. We tweaked Oracle (not particularly the fastest beast on the block), we threw hardware at it. Both options worked... for a while. Next, we denormalized, I believe, 2 joins. Yes, it worked on fetching data - the response time was alright. But it complicated our application, the database design and the stored procedures using it... Not a particular pleasant experience. Then again... years later, I realized that the design should have been different (better logical structure) and these problems would have been avoided... Pity we couldn't do that part again... Learned a lot though. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
This is what I am also saying. The effects of a bad logical DB design will effect you the most only in the long term. In the earlier stages you always trust your own design and always look for additional h/w resources to improve the performance. But in the long term you will realize that there is something other than adding h/w you have to do. That's when we actually realise the mistakes we have done in logical design phase. suhay -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 3:13 PM To: mysql@lists.mysql.com Subject: Re: good database design This is an interesting subject area. In a data warehousing environment, one tends to adopt table structures such as snowflake layouts which lead to improved performance. Createing a perfect normalised database design may well lead to performance issues. If this is the case, go bug the database vendors :-) ... they should give us systems that work properly ... The more joins you have, by far the worse the performance. You may That's a pretty bold statement... need to consider horizontal or vertical table splits. You may need to consider replicating certain data in child tables to avoid joins. I am not saying you do not need to carry out data analysis and gain a full and first hand understanding of the data structures. It is just that when it comes to online performance, sometimes you have to break the rules. But still: logical first, performance later... If at all. I once joined a team that had a running Oracle database and an application on top of it. We were having performance problems and there was the 2 seconds of max response time requirement in the contract. We tweaked Oracle (not particularly the fastest beast on the block), we threw hardware at it. Both options worked... for a while. Next, we denormalized, I believe, 2 joins. Yes, it worked on fetching data - the response time was alright. But it complicated our application, the database design and the stored procedures using it... Not a particular pleasant experience. Then again... years later, I realized that the design should have been different (better logical structure) and these problems would have been avoided... Pity we couldn't do that part again... Learned a lot though. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.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: good database design
I agree totaly to what Sujay Koduri writes : http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design query
I think I have found the solution for my problem. I made the following changes: - I added a new field RecordID in GroupMemberInfo to make the records unique - Instead of MemberID and GroupID, I'm now using MemberName and GroupName. I made this change since in Active Directory every name is unique. - What I found out that in mysql, a FK field can refer to any index field in parent table and not necessarily only Primary Key field. - So instead of making MemberID and GroupID as primarykey, RecordID is primary key now and MemberID is just an index. I don't know if it's a bug in Mysql or it's an added feature that a FK field can refer to any index field in parent table. CREATE TABLE `groupinfo` ( `GroupID` bigint(20) NOT NULL auto_increment, `GroupName` varchar(128) NOT NULL default '', `MemberCount` int(11) default NULL, PRIMARY KEY (`GroupID`), UNIQUE KEY `i_GroupName` TYPE BTREE (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `groupmemberinfo` ( `RecordID` bigint(20) NOT NULL auto_increment, `GroupName` varchar(128) NOT NULL default '', `MemberName` varchar(128) NOT NULL default '', `MemberType` tinyint(4) NOT NULL default '0', PRIMARY KEY (`RecordID`), KEY `i_MemberName` TYPE HASH (`MemberName`), CONSTRAINT `FK_groupmemberinfo_GroupName` FOREIGN KEY (`GroupName`) REFERENCES `groupinfo` (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `hostinfo` ( `HostID` bigint(20) NOT NULL auto_increment, `HostName` varchar(128) NOT NULL default '', `Password` tinyblob NOT NULL default '', PRIMARY KEY (`HostID`), KEY `i_HostName` (`HostName`), CONSTRAINT `FK_hostinfo_HostName` FOREIGN KEY (`HostName`) REFERENCES `groupmemberinfo` (`MemberName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `userinfo` ( `UserID` bigint(20) NOT NULL default '0', `UserName` varchar(128) default NULL, `Password` tinyblob, PRIMARY KEY (`UserID`), KEY `i_UserName` (`UserName`), CONSTRAINT `FK_userinfo_UserName` FOREIGN KEY (`UserName`) REFERENCES `groupmemberinfo` (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I know for sure that this is not the best solution. But now both User and Host are referring to GroupMemberInfo. So I have constraints at DB level Thanks to all who replied, Reema -Original Message- From: Gordon [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 01, 2005 8:39 AM To: 'rtroiana' Subject: RE: Database design query I know what you are trying to do and I can see the logic advantage of having a single table that defines the the group relationship for users hosts and groups. I just don't think the rules governing foreign keys will allow this. Your original thought of enforcing the relationships i.e. cascade delete/update etc. at the application vs the database is the only way I can see to make this happen. Otherwise you are back to three tables with the added code to find all members of a group across the 3 tables. I don't know that I've added much, but I enjoyed the dialog. -Original Message- From: rtroiana [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 4:23 PM To: 'Gordon' Subject: RE: Database design query I can have three different foreign key definitions on one field, but it expects the same value in all the three parent tables. So that's not the right way to implement it What I'm trying to do is: Member id as primary key and UserID, HostID and groupID as foreign keys But since in GroupMember table a member can be in more than 1 group, so I have combination of MemberID, GroupId and MemberType as primary key If I use these 3 as primary key, so UserID, HostID and groupID can't refer MemberID as primary key. That's what the problem is. ++Reema -Original Message- From: Gordon [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 5:14 PM To: 'rtroiana'; mysql@lists.mysql.com Subject: RE: Database design query IF GroupID, HostID and UserID are unique between the three sets then your GroupMember table will work although I would still be tempted to add a MemberType in the GroupMember table. Isn't MemberID the Foreign Key to UserID/HostID/GroupID althugh I don't know if you can have three different foreign key definitions on one field. If not I think you are stuck with 3 tables instead of trying to do it in one. -Original Message- From: rtroiana [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 12:23 PM To: mysql@lists.mysql.com Subject: Database design query Hi All, I'm trying to get data from Active Directory and storing in database. So I have the following tables with their corresponding primary keys: Group (GroupID) Host (HostID) User (UserID) GroupMember(GroupID, MemberID) The relations between them according to Active Directory should be as follows: 1) Host and user can be in one or more groups 2) Groups can be in one or more groups I was trying to implement
Database design query
Hi All, I'm trying to get data from Active Directory and storing in database. So I have the following tables with their corresponding primary keys: Group (GroupID) Host (HostID) User (UserID) GroupMember(GroupID, MemberID) The relations between them according to Active Directory should be as follows: 1) Host and user can be in one or more groups 2) Groups can be in one or more groups I was trying to implement these relations through Db constraints. I wanted HostID and UserID should refer to MemberID as Primary Key. My problems is a foreign key field cannot refer to a part of primary key, so 1) I should add GroupID in Host and User table, which will be redundancy of data, or 2) Instead of adding a new field, I should not have any relations in the database and just implement it in code. I have tried with three different designs, but all of them have some issues. I tied to add a new table just for Member that would store unique memberID. Does it seem like an overhead? I don't if I can just do with existing table or not. I read some articles online, some of them say it's good to implement relations from DB and some say to reduce overhead, relations can be implemented from code. What would the best database practice that you would suggest? I'll appreciate any help Thanks, Reema Duggal Troiana Senior Software Developer BitArmor Systems, Inc. 357 North Craig Street Ground Floor Pittsburgh, PA 15213 [TEL] 412-682-2200 Ext 314 [FAX] 412-682-2201
Re: Database design query
Hi, i think you must normalize your table to more than one table. Users/Groups : N:1 Groups/Groups : N:1 Table Users : User_id Host Group_id Table Groups : Group_id Group_parent_id- is a another group_id No data redondancy and robust implementation. see for example /etc/passwd and /etc/group on a *nix machine. Mathias Selon rtroiana [EMAIL PROTECTED]: Hi All, I'm trying to get data from Active Directory and storing in database. So I have the following tables with their corresponding primary keys: Group (GroupID) Host (HostID) User (UserID) GroupMember(GroupID, MemberID) The relations between them according to Active Directory should be as follows: 1) Host and user can be in one or more groups 2) Groups can be in one or more groups I was trying to implement these relations through Db constraints. I wanted HostID and UserID should refer to MemberID as Primary Key. My problems is a foreign key field cannot refer to a part of primary key, so 1) I should add GroupID in Host and User table, which will be redundancy of data, or 2) Instead of adding a new field, I should not have any relations in the database and just implement it in code. I have tried with three different designs, but all of them have some issues. I tied to add a new table just for Member that would store unique memberID. Does it seem like an overhead? I don't if I can just do with existing table or not. I read some articles online, some of them say it's good to implement relations from DB and some say to reduce overhead, relations can be implemented from code. What would the best database practice that you would suggest? I'll appreciate any help Thanks, Reema Duggal Troiana Senior Software Developer BitArmor Systems, Inc. 357 North Craig Street Ground Floor Pittsburgh, PA 15213 [TEL] 412-682-2200 Ext 314 [FAX] 412-682-2201 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design query
IF GroupID, HostID and UserID are unique between the three sets then your GroupMember table will work although I would still be tempted to add a MemberType in the GroupMember table. Isn't MemberID the Foreign Key to UserID/HostID/GroupID althugh I don't know if you can have three different foreign key definitions on one field. If not I think you are stuck with 3 tables instead of trying to do it in one. -Original Message- From: rtroiana [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 12:23 PM To: mysql@lists.mysql.com Subject: Database design query Hi All, I'm trying to get data from Active Directory and storing in database. So I have the following tables with their corresponding primary keys: Group (GroupID) Host (HostID) User (UserID) GroupMember(GroupID, MemberID) The relations between them according to Active Directory should be as follows: 1) Host and user can be in one or more groups 2) Groups can be in one or more groups I was trying to implement these relations through Db constraints. I wanted HostID and UserID should refer to MemberID as Primary Key. My problems is a foreign key field cannot refer to a part of primary key, so 1) I should add GroupID in Host and User table, which will be redundancy of data, or 2) Instead of adding a new field, I should not have any relations in the database and just implement it in code. I have tried with three different designs, but all of them have some issues. I tied to add a new table just for Member that would store unique memberID. Does it seem like an overhead? I don't if I can just do with existing table or not. I read some articles online, some of them say it's good to implement relations from DB and some say to reduce overhead, relations can be implemented from code. What would the best database practice that you would suggest? I'll appreciate any help Thanks, Reema Duggal Troiana Senior Software Developer BitArmor Systems, Inc. 357 North Craig Street Ground Floor Pittsburgh, PA 15213 [TEL] 412-682-2200 Ext 314 [FAX] 412-682-2201 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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) ---
