Design decision
I have an a table of objects, attached to those objects are keywords. Users submit the keywords to the objects. Currently, I chose to keep a hit count on the keywords, so if a duplicate keyword is supplied, a counter is incremented. I thought this was a good idea, as it keeps the number of rows in the keywords table to a minimum. However, this is a user login based system, and with the above, I lose the ability to track which users sent in which keywords. So I can move to the keywords table storing duplicate keywords, and each keyword will get a user_id attached to it, but, that table will grow. Fast. 1000 objects, each with 10 keywords only = 10,000, I could hit many millions very fast. I could toss in a third table, and relate that to the user_id and keyword. However, I am inserting in one go, and that could be upwards of 30 or more inserts, taking too long. Anyone got any suggestions? Thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design decision
Scott Haneda wrote: I have an a table of objects, attached to those objects are keywords. Users submit the keywords to the objects. Currently, I chose to keep a hit count on the keywords, so if a duplicate keyword is supplied, a counter is incremented. I thought this was a good idea, as it keeps the number of rows in the keywords table to a minimum. However, this is a user login based system, and with the above, I lose the ability to track which users sent in which keywords. Why do you need this info? Is there a reporting need or something else you need to keep this information for? Ie when are you going to need to know who inserted a particular keyword? So I can move to the keywords table storing duplicate keywords, and each keyword will get a user_id attached to it, but, that table will grow. Fast. 1000 objects, each with 10 keywords only = 10,000, I could hit many millions very fast. I could toss in a third table, and relate that to the user_id and keyword. However, I am inserting in one go, and that could be upwards of 30 or more inserts, taking too long. [assuming php] $keyword_ids = array(); foreach ($keywords_to_insert) { $query = insert into keywords_table(keyword) .; $keyword_ids[] = mysql_insert_id(); } $query = insert into table (userid, keywordid) select userid, keywordid where keywordid in ( . implode(',', $keyword_ids) . ); [/assuming php] Just one query to insert the relationship(s) :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design decision
Scott Haneda wrote: I have an a table of objects, attached to those objects are keywords. Users submit the keywords to the objects. Currently, I chose to keep a hit count on the keywords, so if a duplicate keyword is supplied, a counter is incremented. I thought this was a good idea, as it keeps the number of rows in the keywords table to a minimum. However, this is a user login based system, and with the above, I lose the ability to track which users sent in which keywords. Why do you need this info? Is there a reporting need or something else you need to keep this information for? Ie when are you going to need to know who inserted a particular keyword? Because it is a semi-public system, every user is allowed to supply keywords to other users objects. If some not so nice person decides to paste in a list of words to 'game' that object up the ranks, I want to know who, and be able to take action. Looking over the php now, thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design decision
Scott Haneda wrote: Scott Haneda wrote: I have an a table of objects, attached to those objects are keywords. Users submit the keywords to the objects. Currently, I chose to keep a hit count on the keywords, so if a duplicate keyword is supplied, a counter is incremented. I thought this was a good idea, as it keeps the number of rows in the keywords table to a minimum. However, this is a user login based system, and with the above, I lose the ability to track which users sent in which keywords. Why do you need this info? Is there a reporting need or something else you need to keep this information for? Ie when are you going to need to know who inserted a particular keyword? Because it is a semi-public system, every user is allowed to supply keywords to other users objects. If some not so nice person decides to paste in a list of words to 'game' that object up the ranks, I want to know who, and be able to take action. So you're not going to reference the data that much, so size of the table (and speed) isn't going to be a huge issue. Keeping a keywordid - userid table will work pretty well I think. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design decision
I'm not sure why you say 30 or more inserts will take too long. As long as you do a bulk insert, it's just a single command. 30 individual insert will take it's toll. You are really looking for a logging system. Your not going to be querying the table all that much, just a lot of inserts. So millions and millions of rows isn't that big of a deal. Your not deleting anything either, so if you set your parameters right, you can use MyISAM tables without locking issues. You need to set the appropriate parameter to only insert to the end of the table. When your table reaches a certain size (50 million?), you rename it a create an empty one. If you need to query multiple tables after you have a bunch, just create a merge table. I've done a similar setup on a system that added 5-7 million records per day. Alternatively, had a text field that logs all the keywords and a word count field that tells you how many words were entered. You would need to parse the words or use full text indexing to perform analysis, but that would be a common thing I'm guessing. On Oct 3, 2007, at 3:57 AM, Scott Haneda wrote: I have an a table of objects, attached to those objects are keywords. Users submit the keywords to the objects. Currently, I chose to keep a hit count on the keywords, so if a duplicate keyword is supplied, a counter is incremented. I thought this was a good idea, as it keeps the number of rows in the keywords table to a minimum. However, this is a user login based system, and with the above, I lose the ability to track which users sent in which keywords. So I can move to the keywords table storing duplicate keywords, and each keyword will get a user_id attached to it, but, that table will grow. Fast. 1000 objects, each with 10 keywords only = 10,000, I could hit many millions very fast. I could toss in a third table, and relate that to the user_id and keyword. However, I am inserting in one go, and that could be upwards of 30 or more inserts, taking too long. Anyone got any suggestions? Thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- 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]
Design decision
Hi everyone, Just wanted your expert opinion on the following: I'm implementing an authorization system with user/group permissions stored in a database. I have a Users table and a Group table, identical in structure: mysql desc users; mysql desc groups; +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | +---+-+ Now, my question is How to store BEST the relations between users and groups?. Solution 1. I use a separate table with this structure: mysql desc users2groups; +-+-+ | Field | Type| +-+-+ | idUser | int(11) | | idGroup | int(11) | +-+-+ and I add one record for each user -- group mapping. So a SELECT will return potentially many rows for one group or one user. Solution 2. I construct and maintain a string separated by colons (let's say) for each group. So in the users2groups I'd have for example: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | Similary, since I need also user-to-group lookups I construct a string for the group membership of a user so I can have in the same table: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | | 123:456 | 4| Solution 3. Similary to Solution 2 but using the initial tables extended with one more field to accomodate the membership constructed string like: +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | | member_of | text| +---+-+ In Solution 1 I have multiple rows returned. In solution 2,3 I have only one. Solution 1 is scalable however Solution 2,3 can reach (potentially) the limits of the column specification (unlikely though). Assuming I'm interested in maximum speed at the authorization moment (and not at administrative moment), and that I'll have a big number of users and groups, and I access the database via Perl (so no problem to construct/deconstruct strings), what do you think is the best solution? Thank you for your time, Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net I'm not mad. I've been in bad mood for the last 30 years... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design decision
Lian, Between your design solutions (1) and (3), you need to decide, from the logical business requirement, whether the nature of the relationship between user and group is one-to-many (a group may have many users, and each user may belong to exactly one group) or many-to-many (a group may have many users, and each user may belong to multiple groups). For the former, use Solution (3), for the latter, use Solution (1). Granted, Solution (3) is a subset of Solution (1), but requires more resources which might be a waste if you only need represent a one-to-many relationship. Your solution (2) has no restriction on the granularity of the relationship i.e., it can support both; it all depends on your implementation outside SQL, thus is not really a DB schematic means. In this case, the relationship is actually interpreted and maintained by your application program, not by DBMS. In making a choice between Solution (2) and the other two you need to consider the performance difference and code maintenance. Best regards, Lin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 10:22 AM To: mysqllist Subject: Design decision Hi everyone, Just wanted your expert opinion on the following: I'm implementing an authorization system with user/group permissions stored in a database. I have a Users table and a Group table, identical in structure: mysql desc users; mysql desc groups; +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | +---+-+ Now, my question is How to store BEST the relations between users and groups?. Solution 1. I use a separate table with this structure: mysql desc users2groups; +-+-+ | Field | Type| +-+-+ | idUser | int(11) | | idGroup | int(11) | +-+-+ and I add one record for each user -- group mapping. So a SELECT will return potentially many rows for one group or one user. Solution 2. I construct and maintain a string separated by colons (let's say) for each group. So in the users2groups I'd have for example: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | Similary, since I need also user-to-group lookups I construct a string for the group membership of a user so I can have in the same table: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | | 123:456 | 4| Solution 3. Similary to Solution 2 but using the initial tables extended with one more field to accomodate the membership constructed string like: +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | | member_of | text| +---+-+ In Solution 1 I have multiple rows returned. In solution 2,3 I have only one. Solution 1 is scalable however Solution 2,3 can reach (potentially) the limits of the column specification (unlikely though). Assuming I'm interested in maximum speed at the authorization moment (and not at administrative moment), and that I'll have a big number of users and groups, and I access the database via Perl (so no problem to construct/deconstruct strings), what do you think is the best solution? Thank you for your time, Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net I'm not mad. I've been in bad mood for the last 30 years... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design decision
Lin, thanks for your input. Indeed I forgot to mention there is a many-to-many relation between users and groups. I'm inclined though to use Solution 3. My main concern with 2 and 3 was not to exceed the column allocated space for the concatenated string, when it grows with the number of users in a group. I calculated the space requirement for storing a string resulted by 1 milion user IDs each separated by a character. It's taken about 8 milion bytes to store the whole string. A MediumText gives me 16 MB so I think I'm pretty much covered. As I said my main requirements would be speed when a Select is performed. So I prefer to do some additional logic in perl and to retrieve faster results in one Select returning only one row. Hope I'm not wrong ;-) Thanks again, Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net -Original Message- From: Lin Yu [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 9:33 PM To: [EMAIL PROTECTED]; 'mysqllist' Subject: RE: Design decision Lian, Between your design solutions (1) and (3), you need to decide, from the logical business requirement, whether the nature of the relationship between user and group is one-to-many (a group may have many users, and each user may belong to exactly one group) or many-to-many (a group may have many users, and each user may belong to multiple groups). For the former, use Solution (3), for the latter, use Solution (1). Granted, Solution (3) is a subset of Solution (1), but requires more resources which might be a waste if you only need represent a one-to-many relationship. Your solution (2) has no restriction on the granularity of the relationship i.e., it can support both; it all depends on your implementation outside SQL, thus is not really a DB schematic means. In this case, the relationship is actually interpreted and maintained by your application program, not by DBMS. In making a choice between Solution (2) and the other two you need to consider the performance difference and code maintenance. Best regards, Lin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 10:22 AM To: mysqllist Subject: Design decision Hi everyone, Just wanted your expert opinion on the following: I'm implementing an authorization system with user/group permissions stored in a database. I have a Users table and a Group table, identical in structure: mysql desc users; mysql desc groups; +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | +---+-+ Now, my question is How to store BEST the relations between users and groups?. Solution 1. I use a separate table with this structure: mysql desc users2groups; +-+-+ | Field | Type| +-+-+ | idUser | int(11) | | idGroup | int(11) | +-+-+ and I add one record for each user -- group mapping. So a SELECT will return potentially many rows for one group or one user. Solution 2. I construct and maintain a string separated by colons (let's say) for each group. So in the users2groups I'd have for example: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | Similary, since I need also user-to-group lookups I construct a string for the group membership of a user so I can have in the same table: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | | 123:456 | 4| Solution 3. Similary to Solution 2 but using the initial tables extended with one more field to accomodate the membership constructed string like: +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | | member_of | text| +---+-+ In Solution 1 I have multiple rows returned. In solution 2,3 I have only one. Solution 1 is scalable however Solution 2,3 can reach (potentially) the limits of the column specification (unlikely though). Assuming I'm interested in maximum speed at the authorization moment (and not at administrative moment), and that I'll have a big number of users and groups, and I access the database via Perl (so no problem to construct/deconstruct strings), what do you think is the best solution? Thank you for your time, Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net I'm not mad. I've been in bad mood for the last 30 years... -- 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: Design decision on generating data
On Friday 12 April 2002 8:10 pm, Carl Schmidt wrote: I have a form where a user enters some numbers into text boxes. Some of the text boxes can be left blank. The business logic receives all variables to all text boxes. Should I : I'm assuming that you are using an executable file and NOT a web form. Any data you get from a web form, you must treat as suspect and perform business logic checks in another tier. 1.) Validate on the page for the presence of a value, and if not, set the corresponding value to -1 (it will never be this value). Therefore the business logic blindly inserts everything. This is reasonable. Alternatively, set the corresponding value to NULL. 2.) Do not have the variables that correspond to blank text boxes be set to -1. Have the business logic build a query based on which variables are or are not containing a value. In this case some fields for that row may be null. I'd avoid this if you can. Too much dynamicness and it will be hard to test. sql,query. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Design decision on generating data
Thank you for the information. Let me please clarify that i am using a web form. I tried the one solution of converting those values that are not set to null, but mysql complained: SQL: INSERT INTO Development_Event_Show(showID,venueID,description,ageCutoff,ageDelimiter,price1,price2) VALUES('201423cb79b72b5e11','41663c7c59b77da26','',21,,21,) SQL Error: You have an error in your SQL syntax near '21,)' at line 1 Notice the pair of double commas and the one comma at the end? Thats the result of setting those values to null. I thought maybe dynmically building the query would be best because that way: 1.)those values that are to be set as null, simply could be left out of the insert statement. 2.)If each timeI returned a row where values were = -1, then i would have to add extra logic in ther presentation layer to have those values not be displayed. Any thoughts? Carl On Fri, 12 Apr 2002, Christopher Thompson wrote: On Friday 12 April 2002 8:10 pm, Carl Schmidt wrote: I have a form where a user enters some numbers into text boxes. Some of the text boxes can be left blank. The business logic receives all variables to all text boxes. Should I : I'm assuming that you are using an executable file and NOT a web form. Any data you get from a web form, you must treat as suspect and perform business logic checks in another tier. 1.) Validate on the page for the presence of a value, and if not, set the corresponding value to -1 (it will never be this value). Therefore the business logic blindly inserts everything. This is reasonable. Alternatively, set the corresponding value to NULL. 2.) Do not have the variables that correspond to blank text boxes be set to -1. Have the business logic build a query based on which variables are or are not containing a value. In this case some fields for that row may be null. I'd avoid this if you can. Too much dynamicness and it will be hard to test. sql,query. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Design decision on generating data
Usually I build my queries dynamically. I store all the values into a hash ( this is in perl mind you.. ) Then do something like this.. my (@fields, @vals); my ($field_str, $val_str); foreach $field_name (keys %hash) { if ($hash{$field_name}) { push @fields, $field_name; push @vals, $hash{$field_name}; } } $field_str = join(,,@fields); $val_str = join(',',@vals); $val_str = ' . $val_str . '; $dbh-do(INSERT into TABLE ( $field_str ) VALUES ( $val_str )); That's a rather simple ver of it.. you'd probably want to insert some type of data integrity checking in there ( or before you store the values in the hash. ) and it'd be wise to have some other error checking too, but it avoids having to deal with NULL vals. Hope it helps. -- sh On Fri, 2002-04-12 at 19:52, Carl Schmidt wrote: Thank you for the information. Let me please clarify that i am using a web form. I tried the one solution of converting those values that are not set to null, but mysql complained: SQL: INSERT INTO Development_Event_Show(showID,venueID,description,ageCutoff,ageDelimiter,price1,price2) VALUES('201423cb79b72b5e11','41663c7c59b77da26','',21,,21,) SQL Error: You have an error in your SQL syntax near '21,)' at line 1 Notice the pair of double commas and the one comma at the end? Thats the result of setting those values to null. I thought maybe dynmically building the query would be best because that way: 1.)those values that are to be set as null, simply could be left out of the insert statement. 2.)If each timeI returned a row where values were = -1, then i would have to add extra logic in ther presentation layer to have those values not be displayed. Any thoughts? Carl On Fri, 12 Apr 2002, Christopher Thompson wrote: On Friday 12 April 2002 8:10 pm, Carl Schmidt wrote: I have a form where a user enters some numbers into text boxes. Some of the text boxes can be left blank. The business logic receives all variables to all text boxes. Should I : I'm assuming that you are using an executable file and NOT a web form. Any data you get from a web form, you must treat as suspect and perform business logic checks in another tier. 1.) Validate on the page for the presence of a value, and if not, set the corresponding value to -1 (it will never be this value). Therefore the business logic blindly inserts everything. This is reasonable. Alternatively, set the corresponding value to NULL. 2.) Do not have the variables that correspond to blank text boxes be set to -1. Have the business logic build a query based on which variables are or are not containing a value. In this case some fields for that row may be null. I'd avoid this if you can. Too much dynamicness and it will be hard to test. sql,query. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Bug in 3.23.33? Or design decision?
Hi! I just upgraded from 3.22.26a to 3.23.33. We've got a database that has a table that has a column with the name: FullText. Unfortunately, now with 3.23.33, any queries that explicitly refer to this column/field return an SQL error. So far this has been tested with SELECT and UPDATE. Is this expected behavior? Or is this an actual bug? Eric A. Stewart - Network Administrator - [EMAIL PROTECTED] University of South Florida, Tampa Campus Library - Technology Work: http://www.lib.usf.edu/ | MS/MIS | ICQ# Play: http://cerebus.lib.usf.edu/ | Graduate Student | 31285736 SSI MD#100762 Mail not in plain text is annoying. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Bug in 3.23.33? Or design decision?
fulltext is now a reserved word - either change the name of the field, or use quotes when refering to it. regards, P On Tue, 6 Mar 2001 [EMAIL PROTECTED] wrote: Hi! I just upgraded from 3.22.26a to 3.23.33. We've got a database that has a table that has a column with the name: FullText. Unfortunately, now with 3.23.33, any queries that explicitly refer to this column/field return an SQL error. So far this has been tested with SELECT and UPDATE. Is this expected behavior? Or is this an actual bug? Eric A. Stewart - Network Administrator - [EMAIL PROTECTED] University of South Florida, Tampa Campus Library - Technology Work: http://www.lib.usf.edu/ | MS/MIS | ICQ# Play: http://cerebus.lib.usf.edu/ | Graduate Student | 31285736 SSI MD#100762 Mail not in plain text is annoying. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Antwort: Bug in 3.23.33? Or design decision?
On 06.03.2001 19:05:34 iso-8859-1 wrote: Is this expected behavior? Or is this an actual bug? I suppose this may be expected behavior, as FullText now is a reserved word in MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php