How to create a relationship to another row in the same table
I'm having a hard time trying to find the best way to create a relationship between two rows in the same table. I have a database that describes network equipment. There is a table called devices that contains information about the device. There is another table called ports that describe different types of ports (electric, ethernet, serial) and so on. It has a foreign key to the device. What I need now is a way to connect two ports together. So for instance, if it was an ethernet port the connection would be from perhaps a server to a switch. I could have a table (connections) with 2 rows in it for each entry (from server port to switch port and another one from the switch port back to server port) but this seems redundant. If I create a cross reference table with a primary key on (port_id_a, port_id_b) it still allows me to recreate the same connection but in the reverse order. This should not be allowed since we can only connect ports 1 to 1. I'vr thought about keeping the connected port relationship in the ports table and updating two rows every time a change is made. But once again, I'm thinking this should be down using only 1 row. Any of any ideas on an elegant database structure to do this? Thanks in advance for your input, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Transations? (was: Re: login issue..pls help)
Hi, (better change the message subject if you change, well, the subject) user will create more number of tickets per day. so INSERT, UPDATE, SELECT will use highly. in document says' Myisam will not support transaction but it has self backup and restoration method. but in InnoDB good for transaction but no self managed backup and restoration.. now how can i choose my engine. ? my usage is only storage and retrieve data.. so which one is normally preferred by all developers Quite simple really: do you need to store data in one table that depends on other data in other tables? If the answer is YES, then use transactions. Without transactions, you cannot guarantee the data consistency. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=arch...@jab.org
GRANT USAGE (was: Re: login issue..pls help)
what is the purpose of 'Grant usage ' command http://search.mysql.com/search?site=refman-50q=grant+usagelr=lang_en Check the documentation, if you have more questions after that, get back to us. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=arch...@jab.org
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
