-----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 unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
