Re: ensuring an instructor, client, or room isn't double-booked
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Benjamin, et al -- ...and then Benjamin Pflugmann said... % % Hi David. Hi! % % On Sun 2002-12-29 at 16:21:56 -0500, [EMAIL PROTECTED] wrote: % ...and then Benjamin Pflugmann said... % [...] % No; everything is one hour. Two hours is two bookings. % % Ah. Good. That makes the case easier and is exactly the case my last % answer was about. OK. % % [...] % % know beforehand when the class starting at x o'clock will end? And % % saving a number for the time slot would also work? % % Saving a number? I don't know what you mean here... % % Not important anymore. Ah; good :-) % % But for completeness: You could do as with school classes. 9-10 % o'clock is 1st, 10-11 is 2nd and so on. So you could theoretically % save date 2003-02-10 class 1 instead of date 2003-02-10 time % 09:00:00. Oh, I see... I've been thinking about that, too; I currently use a datetime field, but all I really need is a date and an hour. I figure with one combined field I can match against -YY-ZZ% but maybe two fields would make it easier to pull out the time column... % % Not that my suggestion would be better necessarily, it just served to % make it less ambiguous what we are talking about. Seemed it failed. ;) Hah :-) Well, thanks, anyway! % % [...] % % In the case of time slots, you could create a unique index on % % (client,time) and (instr,time) and skip the selects. Simply insert and % % check for an duplicate key error. % % Hmmm... OK; avoiding duplicates is the end goal and so I should probably % learn more about this apparently simple approach. % % Yes, the main purpose of UNIQUE indexes is to guarantee uniqueness in % a column (or a set of columns). So that's the way to go. Oh, I see! Cool. Now I presume that I will want a unique index for (instructor,timeslot) and a[nother] unique index for (client,timeslot). I can't do a unique index on the place because some rooms are booked for groups (I'm planning to create 12 records for a 12-seat class when it's defined, and then I'll update those records until there are no more with empty client values as I add students). Do I want two unique indexes like that, or some other approach? % % Um, what next? :-) % % :-) Hee hee. % % This is probably in the mysql doc, so a pointer would be appreciated % but know that I will [try to] look up multi-column indexes and see % how they deal with duplicates. % % http://www.mysql.com/doc/en/Multiple-column_indexes.html Cool; thanks. % % Although the manual covers a lot of stuff which is not strictly % MySQL-related, it is not well suited to be an introduction into the % world of relational databases. Yeah. I've been drawing heavily on past theory classes, but that's now ten years old :-) % % May I suggest Paul DuBois' book? It provides a lot of insight in such % issues. You may. If you know anyone who wants to buy it for me for a belated Christmas present, you can suggest that, too :-) OK, OK... Having found out about it, I think I will probably go and get it; it sounds like a good investment. Even if I start with a borrowed copy from a library I want to get my hands on it. % % [...] % I figured I'd have to check every record to see if the datetime and ... % many of them), and maybe not with the indexes (hmmm... indices?) you % mention above. % % Well, that is what indexes are all about. Searching for 1 record in OK... Neat. % 1 billion with indexes can be as fast as a full table scan for 1 in 30 % records. Good :-) % % A more complex answer from some ealier posts of mine (still % simplified): % % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:77486 % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:115591 Thanks; I'll read them next. [Actually I just popped out and read them.] Oh, I get it; an index is a fast BTree pointer to the data; it's a hash table! Cool. Yes, that's what I want, all right. % % I don't know exactly, but we're probably looking at a total of 3k or 4k ... % that a few or a lot? % % It depends on how many time slots you have. The general answer is that % you want the column with the highest cardinality first. But also the % storage size matters and so on. That's what etc was about. ;-) Heh :-) % % Usually I use the trial and error method I mentioned below, myself. OK. % But sometimes it is so obvious that you don't have to. Like if you had % only 40 time slots (only planning a week ;). The client should come % first. Yeah. I won't have that few; I'll have about 80 per week (6a thru 7p on six days) for every week from now until the end of time (hey, I can hope that I'll be making money from this software for that long!). So in the beginning I'll have fewer time slots, but by the end of the first year that will very probably have flipped... Good grief; what do you do in that case? Well, I suppose it's an opportunity to charge for an annual tuneup ;-) % ... % I
Re: ensuring an instructor, client, or room isn't double-booked
David, On Tue 2002-12-31 at 06:47:38 -0500, [EMAIL PROTECTED] wrote: [...] Oh, I see... I've been thinking about that, too; I currently use a datetime field, but all I really need is a date and an hour. I figure with one combined field I can match against -YY-ZZ% but maybe two fields would make it easier to pull out the time column... well, using one field looks fine to me. Both approaches have their advantages and disadvantages and unless you see a particular problem (sounds familiar? ;-), just go with the simpler solution (one column). [...] Now I presume that I will want a unique index for (instructor,timeslot) and a[nother] unique index for (client,timeslot). I can't do a unique index on the place because some rooms are booked for groups (I'm planning to create 12 records for a 12-seat class when it's defined, and then I'll update those records until there are no more with empty client values as I add students). Do I want two unique indexes like that[...]? Correct. [...] You may. If you know anyone who wants to buy it for me for a belated Christmas present, you can suggest that, too :-) :-) [...] % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:77486 % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:115591 Thanks; I'll read them next. [Actually I just popped out and read them.] Oh, I get it; an index is a fast BTree pointer to the data; it's a hash table! Cool. Yes, that's what I want, all right. Just a minor clarification: it's *like* a hash table in its *purpose*. Actually, MySQL even supports real hashes for in-memory tables (TYPE=HEAP), so I thought it's worth pointing out. [...] Yeah. I won't have that few; I'll have about 80 per week (6a thru 7p on six days) for every week from now until the end of time (hey, I can hope that I'll be making money from this software for that long!). So in the beginning I'll have fewer time slots, but by the end of the first year that will very probably have flipped... Good grief; what do you do in that case? Depends (familiar, isn't it? ;-). From what I heard until yet, I assume that MySQL will easily handle your database, so having a sub-perfect index should be fine. So go long-term, since with fewer rows the index doesn't matter that much. Well, I suppose it's an opportunity to charge for an annual tuneup ;-) [...] Of course. ;-) If it is anything like the projects I know, you will correcting your application to client's wishes long before a year has passed. HTH and a HNY ;) Benjamin. -- [EMAIL PROTECTED] - 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: ensuring an instructor, client, or room isn't double-booked
Hi David. On Sun 2002-12-29 at 16:21:56 -0500, [EMAIL PROTECTED] wrote: ...and then Benjamin Pflugmann said... [...] No; everything is one hour. Two hours is two bookings. Ah. Good. That makes the case easier and is exactly the case my last answer was about. [...] % know beforehand when the class starting at x o'clock will end? And % saving a number for the time slot would also work? Saving a number? I don't know what you mean here... Not important anymore. But for completeness: You could do as with school classes. 9-10 o'clock is 1st, 10-11 is 2nd and so on. So you could theoretically save date 2003-02-10 class 1 instead of date 2003-02-10 time 09:00:00. Not that my suggestion would be better necessarily, it just served to make it less ambiguous what we are talking about. Seemed it failed. ;) [...] % In the case of time slots, you could create a unique index on % (client,time) and (instr,time) and skip the selects. Simply insert and % check for an duplicate key error. Hmmm... OK; avoiding duplicates is the end goal and so I should probably learn more about this apparently simple approach. Yes, the main purpose of UNIQUE indexes is to guarantee uniqueness in a column (or a set of columns). So that's the way to go. Um, what next? :-) :-) This is probably in the mysql doc, so a pointer would be appreciated but know that I will [try to] look up multi-column indexes and see how they deal with duplicates. http://www.mysql.com/doc/en/Multiple-column_indexes.html Although the manual covers a lot of stuff which is not strictly MySQL-related, it is not well suited to be an introduction into the world of relational databases. May I suggest Paul DuBois' book? It provides a lot of insight in such issues. [...] I figured I'd have to check every record to see if the datetime and client (or instructor) matched what I have already, and a few years from now when that table is long it seems like that would take a while (seconds? minutes? i dunno) to query... Maybe not at all on such a simple table (very short fixed-length fields, and not very many of them), and maybe not with the indexes (hmmm... indices?) you mention above. Well, that is what indexes are all about. Searching for 1 record in 1 billion with indexes can be as fast as a full table scan for 1 in 30 records. A more complex answer from some ealier posts of mine (still simplified): http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:77486 http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:115591 [...] % If not: Whether an index (client,time) or (time,client) is better % depends on your data (few or a lot of client, etc). If you don't have I don't know exactly, but we're probably looking at a total of 3k or 4k clients with some .5k actively making frequent bookings, and up to a dozen instructors (per installation, which for starters will be a little PII-266 with only 64M of RAM and a 5G IDE disk but will probably settle on a cheapo PIII-800 with 128M and a 10G IDE disk at each studio). Is that a few or a lot? It depends on how many time slots you have. The general answer is that you want the column with the highest cardinality first. But also the storage size matters and so on. That's what etc was about. ;-) Usually I use the trial and error method I mentioned below, myself. But sometimes it is so obvious that you don't have to. Like if you had only 40 time slots (only planning a week ;). The client should come first. % I you are not sure, simply build all of them and look which one get % used. Then dropped the other ones again. The same applies for instr. That's a safe approach that should get the questions answered :-) Jupp. [...] I don't even know; see my newbie comment above :-) I thought that a temporary table helps to speed up specific queries because the grouping is already done and then you just select from that, but I dunno :-) That is correct. *If* you need grouping. And then, as you said, only for some specific queries. But you don't (need grouping). You are right, that you have kind of groups in the table. But the term grouping is about when you want to retrieve some fact based on groups. E.g. if you want to know the next class each client attends, similar to this: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html But I think the overall answer is: Don't optimize early. You are right, that you need a clean design to get reasonable speed. I suggest you first care about that and think about specific optimizations (like using temporary tables) only stumble upon a specific bottleneck. It sounds to me as if you are worrying about non-issues currently. But, of course, a basic understanding of what is going on never hurts. HTH, and yes, HAND, too, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/
Re: ensuring an instructor, client, or room isn't double-booked
Hi. On Sat 2002-12-28 at 08:15:58 -0500, [EMAIL PROTECTED] wrote: [...] create table schedule ( # ID number id smallint not null default 0 auto_increment primary key , client smallint , # references client.id class smallint , # references classtypes.id place smallint , # references places.id instr smallint , # references personnel.id time datetime # when Don't you need some kind of duration? Or are the times fixed and you know beforehand when the class starting at x o'clock will end? And saving a number for the time slot would also work? ) ; I could, for every insert, check select * from schedule where client = '1' and time = '...' ; to make sure the client isn't being booked twice, or select * from schedule where instr = '1' and time = '...' ; to make sure the instructor isn't double-booked, but In the case of time slots, you could create a unique index on (client,time) and (instr,time) and skip the selects. Simply insert and check for an duplicate key error. that seems like it would get awfully slow as the table grows... I wonder why you think so. Maybe I do not understand your concern correctly, but with the right indexes this shouldn't be slow. Do I miss something? If not: Whether an index (client,time) or (time,client) is better depends on your data (few or a lot of client, etc). If you don't have time slots, even (client) or (time) would help, although not as much. I you are not sure, simply build all of them and look which one get used. Then dropped the other ones again. The same applies for instr. Is this the sort of thing where a temporary table is advised, or should I define a reverse table that shows each instructor and his/her bookings, or what? I am not sure how a temporary table would improve things. So we have some kind of misunderstanding. Could you post how you would use temporary tables here? Then I could easier tell, how I would do the same. HTH, Benjamin. -- [EMAIL PROTECTED] - 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: ensuring an instructor, client, or room isn't double-booked
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Benjamin, et al -- ...and then Benjamin Pflugmann said... % % Hi. % % On Sat 2002-12-28 at 08:15:58 -0500, [EMAIL PROTECTED] wrote: % [...] %create table schedule %( % # ID number % id smallint not null default 0 auto_increment primary key , % client smallint , # references client.id % class smallint ,# references classtypes.id % place smallint ,# references places.id % instr smallint ,# references personnel.id % time datetime # when % % Don't you need some kind of duration? Or are the times fixed and you No; everything is one hour. Two hours is two bookings. If that changes, I'll have to decide whether to use a datetime for the end or just a duration... % know beforehand when the class starting at x o'clock will end? And % saving a number for the time slot would also work? Saving a number? I don't know what you mean here... % ... %select * from schedule where client = '1' and time = '...' ; ... %select * from schedule where instr = '1' and time = '...' ; ... % % In the case of time slots, you could create a unique index on % (client,time) and (instr,time) and skip the selects. Simply insert and % check for an duplicate key error. Hmmm... OK; avoiding duplicates is the end goal and so I should probably learn more about this apparently simple approach. Um, what next? :-) This is probably in the mysql doc, so a pointer would be appreciated but know that I will [try to] look up multi-column indexes and see how they deal with duplicates. % % that seems like it would get awfully slow as the table grows... % % I wonder why you think so. Maybe I do not understand your concern % correctly, but with the right indexes this shouldn't be slow. Do I % miss something? Probably not. Quite on the contrary, I should probably start every post with I am a newbie and don't know at all what I'm talking about, so ignore my blatant errors and incorrect suggestions and just read my mind instead :-) I figured I'd have to check every record to see if the datetime and client (or instructor) matched what I have already, and a few years from now when that table is long it seems like that would take a while (seconds? minutes? i dunno) to query... Maybe not at all on such a simple table (very short fixed-length fields, and not very many of them), and maybe not with the indexes (hmmm... indices?) you mention above. I know that mysql (like other databases) can handle umpteen million rows in oodles of tables and such, perhaps with a bit of performance tuning, but snappy response is very important for this application and I'll need to at least design the database well to help ensure that. % % If not: Whether an index (client,time) or (time,client) is better % depends on your data (few or a lot of client, etc). If you don't have I don't know exactly, but we're probably looking at a total of 3k or 4k clients with some .5k actively making frequent bookings, and up to a dozen instructors (per installation, which for starters will be a little PII-266 with only 64M of RAM and a 5G IDE disk but will probably settle on a cheapo PIII-800 with 128M and a 10G IDE disk at each studio). Is that a few or a lot? % time slots, even (client) or (time) would help, although not as much. % % I you are not sure, simply build all of them and look which one get % used. Then dropped the other ones again. The same applies for instr. That's a safe approach that should get the questions answered :-) % % Is this the sort of thing where a temporary table is advised, or % should I define a reverse table that shows each instructor and % his/her bookings, or what? % % I am not sure how a temporary table would improve things. So we have % some kind of misunderstanding. Could you post how you would use % temporary tables here? Then I could easier tell, how I would do the % same. I don't even know; see my newbie comment above :-) I thought that a temporary table helps to speed up specific queries because the grouping is already done and then you just select from that, but I dunno :-) % % HTH, % % Benjamin. % % -- % [EMAIL PROTECTED] Thanks HAND Happy Holidays :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+D2d0Gb7uCXufRwARAkA9AJ4/j5DHIoEhMjRcU8HGl71jkGS3JgCgwfGZ hZCRe+9AqoOV+pb7y/YG8sI= =NsIe -END PGP SIGNATURE- - 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
ensuring an instructor, client, or room isn't double-booked
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I have tables for clients, instructors, places (rooms), and classtypes, and finally a schedule where these come together. I'd like to make sure that a client isn't being double-scheduled (two classes at once), or an instructor isn't being double-booked (two private students in the same time slot) -- basically the same person (client or instructor) at the same time, though it also applies for rooms. Given a table like create table schedule ( # ID number id smallint not null default 0 auto_increment primary key , client smallint , # references client.id class smallint ,# references classtypes.id place smallint ,# references places.id instr smallint ,# references personnel.id time datetime # when ) ; I could, for every insert, check select * from schedule where client = '1' and time = '...' ; to make sure the client isn't being booked twice, or select * from schedule where instr = '1' and time = '...' ; to make sure the instructor isn't double-booked, but that seems like it would get awfully slow as the table grows... Is this the sort of thing where a temporary table is advised, or should I define a reverse table that shows each instructor and his/her bookings, or what? TIA HAND Happy Holidays :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+DaQNGb7uCXufRwARAg6qAJ4vjNsp5EfoQLJRKJdw0MhMx56fygCbBX7H MVGjrhB5RczZ5f6UAFf6bmk= =VSZ0 -END PGP SIGNATURE- - 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
