Re: Design decision

[Brent Baisley]

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
 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]

Re: Design decision

[Chris]

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

[Scott Haneda]

> 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
 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

[Chris]

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

[csebe]

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

[Lin Yu]

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 on generating data

[Steven Hajducko]

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

Re: Design decision on generating data

[Carl Schmidt]

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

[Christopher Thompson]

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