Question of Relationship between tables
Hello, I'm having conceptualizing the correct relationship for what seems a very simple scenario: Scenario: I have a standard USERS table... USERS have a list of FRIENDS, these can be other members or also non members... Similar to facebook... My main issue is conceptualizing the relationship for member to member contacts. TABLES: USER: emailID (PK) userName Password Address Etc FRIEND: emailID (PK) friendEmailID (PK) RELATIONSHIPS: USER.emailID (1) --- FRIEND.emailID (many) USER.emailID (many) --- FRIEND.friendEmailID (1) Does this work or is this a cyclical many-to-many relationship? (1 User can have many friends, 1 friend can belong to many users)... If so, what's the correct (normalized) way of representing this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Keys
Does using foreign keys simply enforce referential integrity OR can it also speed up JOIN queries? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
I know that this is the standard means of dealing with a many to many relationship, I'm just not sure it fits here. USER: emailID (PK) userName Password Address Etc FRIEND: emailID (PK) USER_FRIEND user_emailID (PK) friend_emailID (PK) So if I want a list of USER [EMAIL PROTECTED]'s friends: SELECT friend_emailID from USER_FRIEND WHERE user_emailID=[EMAIL PROTECTED] In this (and pretty much every case), the FRIEND table is useless and doesn't make sense logically. *I THINK I EXPLAINED THINGS INCORRECTLY* Let me try again: * I'm not sure if it's even a true many to many relationship as this is actually a relationship between ONE RECORD in a TABLE and a ANOTHER RECORD in THE SAME TABLE! A USER is: USER emailID userName A friend is really just another RECORD in the USER table. i.e. IF I HAD TO MAP THE RELATIONSHIP LOGICALLY, IT WOULD BE: USER: emailID userName |1 |many FRIEND: emailID |many |1 USER: emailID username (i.e. it's two records in the same USER table) How are relationships between records in the same table usually dealt with in terms of design? Implementation? ThanX, Ben Jim Lyons [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Indexes speed up joins. Foreign keys should be indexes themselves, so they can also speed up joins. If the FK is not an index, it won't help. So, index your FKs On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. [EMAIL PROTECTED] wrote: Does using foreign keys simply enforce referential integrity OR can it also speed up JOIN queries? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relational Databasing on busy webserver
It sounds like what you're looking for is an ENUM value: http://dev.mysql.com/doc/refman/5.0/en/enum.html Bear in mind when using this data-type that if you do want to add a new value (such as a new state/country), you will have to perform an ALTER TABLE statement, which can take some time to execute over a table storing a lot of data. Using the relational method means you'd just have to add one row to a table, which is significantly faster. Just to clarify: Wouldn't the ALTER TABLE though just have to be run once? and on the backend? I guess what I'm asking is: would this affect web-users in any way? Thanks On Mon, 2008-09-22 at 17:12 -0400, Ben A.H. wrote: Hello, We are setting up a relatively common web application which collects user information... Right off the bat our system will have over 200,000 USER RECORDS so having an efficient database lookup scheme is critical. I am a programmer/developer with some education in databasing but my forte is programming hence this odd (I think) question... Obviously we'll have the standard USER table a bunch of supporting tables. For items like STATE/PROVINCE standard database logic would dictate I setup: USER name email .etc... StateID (foreign key) STATE StateID StateName But I often wonder if there's any benefit in having a State table... For one, new States/Provinces are not that likely, removal of these entities is also unlikely (except maybe Quebec :-)) so the chances of having to make alternations to the State table near nil. It raises the question of whether or not a State Table is even necessary. Using a traditional SQL State table, I'd have to do an SQL query to populate User State/Province options every time the New User Registration form is shown - isn't this needless overhead?! Would my webforms not load faster if State/Province information was hard-coded as options for a combobox? Is this something people have experimented with? There are various other fields that I believe could be handled like this for a cumulative performance boost. For example: country, state/province, gender, industry, occupation, ethnicity, language are all options that aren't going to change that often. Then again, when we do have to display the users choice; for example if user.countryID=3 we'd have to have a way to turn that number 3 into the word Canada when we display the users profile... I'd probably do this via XML lookup. Has anyone experimented with the benefits/tradeoffs of such a scheme? As I've said, we are dealing with at least 200,000 user records, probably 300,000-400,000 in the next year. The User table contains at least 50 attributes, 15 of which are Foreign Keys that link to tables that will likely never change (the users choices for countryID, stateID, OperatingSystemID, internet_connectionTypeID, internetUserFrequencyID, industryID, occupationID)... ThanX in advance Ben -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relational Databasing on busy webserver
Hello all, Thank-you for all of your help, I was really surprised by the speed quality of responses. Below is a table I've created based on some reading I did following everyone's suggestions (I hope the table shows correctly)... I'm leaning towards the pure Relational as I like having all data in a singular, easily conceptualized exported datastore but we may go with the ENUM approach if it provides a performance boost. Feel free to comment... Relational Enum Scripted Maintainability + (Simplest, known in industry, other programmers can easily understand/maintain) + (Almost as simple as Relational, known in industry, other programmers can easily understand/maintain) - (Will have 2 datastores, the SQL DB XML or hard-coded values; NOT complicated, but not as intuitively maintainable) Scalability + (Easily Scalable, simplified by the fact there's 1 datastore. Adding a State as easy as adding record to db, no code changes needed!) + (Easily Scalable, simplified by the fact there's 1 datastore. Adding a State as easy as adding new ENUM to db, no code changes needed!) N/- (Not as Easily scalable, adding/removing field means changing code which always has risks; of course done right: we'd just add new code to an XML file) Code Complexity + (Super simple, easy to understand, GET DATA:connect, query, show; WRITE DATA validate, connect, write) + (Super simple, easy to understand, GET DATA:connect, query, show; WRITE DATA validate, connect, write) - (Alittle more involved GET DATA: connect, query, turn codes into words via code, showd WRITE DATA: similar to Relational/Enum Performance N or - N or - + Could potentially increase performance by taking load off SQL server. Portability (Using w/ 3rd party System or future external application) + Integrating w/ 3rd party SW or new applications simple. + (?) Integrating w/ 3rd party SW or new applications probably like Relational. Are ENUMS handled/returned via normal SELECT queries? (if so, just like relational) - Would have to write the decodify module for any outside system. Isn't intuitively portable. ThanX, Ben David Ashley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, Sep 23, 2008 at 11:13 AM, Olexandr Melnyk [EMAIL PROTECTED] wrote: On 9/23/08, David Ashley [EMAIL PROTECTED] wrote: For example, for the 50 states, a lot of programmers would put this logic in the web script and just store the two-letter postal code in the database table (but with no separate table for states). The mapping from MI to Michigan would occur in the web scripts; as would the logic to know that XQ is not a state. Keep in mind that, as you add more countries, maintaining in-scipt code/name mappings of state codes will become more and more cumbersome. Another problem will arise if you want to view user information, along with full country and state names, from a different programming language. Plus, if the same query is run very often and table is almost static, chances are high that the result will be in query cache. The different programming language issue is one I hadn't thought about. Good catch. I program in PHP only, and I run queries manually only if I need to debug something hard. But I could see the issue you cited arising in a more complex environment. I could even see a mixed web application where the web presence is a mixture of PHP, Python, and Perl. Good catch. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relational Databasing on busy webserver (Benchmark of Enum?!)
I figured that was what you meant... I guess my table didn't work (see above message...don't ya' love plaintext :-O)... Has anyone ever tried to benchmark the difference between utilizing ENUMs vs. traditional relational databasing? I would think ENUM is ideal for items I specified at the beginning of this thread, items I would think would be part of MANY (if not MOST) databases (state, country, gender, industry, occupation, referredFrom, ethnicity, position)... In my case, it would allow me to eliminate 15+ tables... I'm just wondering why database ENUMS aren't used more often... (what's the catch) Olexandr Melnyk [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, Sep 23, 2008 at 6:13 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: Plus, if the same query is run very often and table is almost static, chances are high that the result will be in query cache. Just realized that I haven't mentioned that this sentence is related to storing states in the database, rather than in the application layer. -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relational Databasing on busy webserver
Hello, We are setting up a relatively common web application which collects user information... Right off the bat our system will have over 200,000 USER RECORDS so having an efficient database lookup scheme is critical. I am a programmer/developer with some education in databasing but my forte is programming hence this odd (I think) question... Obviously we'll have the standard USER table a bunch of supporting tables. For items like STATE/PROVINCE standard database logic would dictate I setup: USER name email .etc... StateID (foreign key) STATE StateID StateName But I often wonder if there's any benefit in having a State table... For one, new States/Provinces are not that likely, removal of these entities is also unlikely (except maybe Quebec :-)) so the chances of having to make alternations to the State table near nil. It raises the question of whether or not a State Table is even necessary. Using a traditional SQL State table, I'd have to do an SQL query to populate User State/Province options every time the New User Registration form is shown - isn't this needless overhead?! Would my webforms not load faster if State/Province information was hard-coded as options for a combobox? Is this something people have experimented with? There are various other fields that I believe could be handled like this for a cumulative performance boost. For example: country, state/province, gender, industry, occupation, ethnicity, language are all options that aren't going to change that often. Then again, when we do have to display the users choice; for example if user.countryID=3 we'd have to have a way to turn that number 3 into the word Canada when we display the users profile... I'd probably do this via XML lookup. Has anyone experimented with the benefits/tradeoffs of such a scheme? As I've said, we are dealing with at least 200,000 user records, probably 300,000-400,000 in the next year. The User table contains at least 50 attributes, 15 of which are Foreign Keys that link to tables that will likely never change (the users choices for countryID, stateID, OperatingSystemID, internet_connectionTypeID, internetUserFrequencyID, industryID, occupationID)... ThanX in advance Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
