Re: join help: i am lost
David, I am unsure if I followed your example completely, but maybe this might help. Not knowing your complete database structure, I am unsure if my comments will be entirely valid but here goes. I think you could achieve your goal if you think of your groups as containing one or many clients. Each single client would be in a group of their own. Yes, this is a little strange, but it makes the structure a lot easier and consistent. So this is what you would have: Time slot -- class -- group -- client So the structure, in english: Each time slot has a one-to-many relationship to classes Each class has a one-to-many relationship to groups Each group has a one-to-many relationship to clients Now, you can query the database and see how many time slots have more than one class. You no longer need to worry about double booking. Hope this helps At 11:23 AM 1/1/03 -0500, David T-G wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I need, I think, some pointers to basic JOIN tutorials. I don't really know how to approach this query. I should say early on that I don't expect the list to write my code for me, though any help anyone can send is VERY much appreciated; rather, I don't even know where to look to do the required reading :-) I have a schedule table that looks like create table schedule ( # ID number id smallint not null default 0 auto_increment primary key , class smallint not null , # references classtypes.id client smallint not null , # references client.id instr smallint not null , # references personnel.id place smallint not null , # references places.id timeslot datetime not null ,# when #unique (timeslot,client) , # no double-bookings #unique (timeslot,instr) , # no double-bookings index (timeslot,client) , # no double-bookings index (timeslot,instr) ,# no double-bookings index (timeslot,place) ,# cannot be unique 'cuz of group classes cancelled datetime not null # cancelled? when? ) ; that holds my bookings. Classes can be either private (one client) or group (some number N, though perhaps only 1 client will sign up). I started out, as you can see, with unique indexes for the client and instructor, but since I had the brilliant idea of creating some N rows for a group class, all with empty client fields, that doesn't work. I'm not so worried about that; it just means that I'm going to have to do some work on my own to ensure no double-bookings (except for a group class). [OK, so maybe it wasn't that brilliant; better approaches will be heard with avid interest. But it worked in my *head*! :-] Then I had the idea of using client id '0', which will never occur in the client table (create table clients ( id smallint unsigned not null default 0 auto_increment primary key , ...);), as a way to black out a time slot so that the instructor can be guaranteed a lunch break or so. That's where my problem really came up. Thinking at first only of an instructor and single clients I came up with (deep breath) select substring(s.timeslot,1,13) , concat(c.fname,' ',c.lname) from personnel as i , clients as c , schedule as s where i.id = s.instr and c.id = s.client and i.fname = 'penelope' order by timeslot; which gives me a lovely ++-+ | substring(s.timeslot,1,13) | concat(c.fname,' ',c.lname) | ++-+ | 2002-12-27 06 | david t-g | | 2002-12-27 07 | david t-g | | 2002-12-27 10 | david t-g | | 2002-12-27 11 | harmon | | 2002-12-27 13 | larry thorburn | ++-+ and I write my table in php without a second thought. Then, however, comes the mess of pulling out any records where the client id is 0; for every timeslot like that, I get a row for each client in the clients table! Here's where I'm really swamped. I can write a separate query for schedule records where the client is 0, and I can write a separate query for schedule records where the class is not private, but how can I combine all three to get one lovely result to use to build my table? TIA HAND Happy New Year mysql query, :-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://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+ExYNGb7uCXufRwARAp0EAJ9a5C3emiflZGtuiXPOcWnMJo7iXQCeKVMw 0w2kNXILUltbWs/rxUwG22E= =kYIn -END PGP SIGNATURE- - Before posting, please
Re: join help: i am lost
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Frank, et al -- ...and then Frank Peavy said... % % David, % I am unsure if I followed your example completely, but maybe this might % help. Not knowing your complete database structure, I am unsure if my % comments will be entirely valid but here goes. I didn't want to throw the whole thing to the list; I thought that might be considered rude :-) % % I think you could achieve your goal if you think of your groups as Hmmm... You mean like a group class? % containing one or many clients. Each single client would be in a group of % their own. Yes, this is a little strange, but it makes the structure a lot Not necessarily; I already think of a private instruction as basically the same as a group instruction except only one slot. I don't know that I've managed to *write* the schema that way, but that's how it's in my head ;-) % easier and consistent. So this is what you would have: % % Time slot -- class -- group -- client % % So the structure, in english: % Each time slot has a one-to-many relationship to classes % Each class has a one-to-many relationship to groups % Each group has a one-to-many relationship to clients Here's where I'm not sure how to make that fit -- probably just because of my own terminology. A scheduling, or a booking, eventually has to have a class type (private or one of many groups -- so I suppose I could simply make a group class type 'private' and that type has only one slot), an instructor, a place, a time slot, and the client or clients to go in it. I currently have a clients table, a classtypes table (various types of group classes), a personnel table (instructors), a places table (what room), and a schedule table (drawing from each of these plus a timeslot field). When I have a private classs I just leave the classtype empty (but I'm open to change). If I get you right, I'd have a class table pulling together the type of class (one of the typical group classes or this new 'private' one) and, somehow, the client(s) enrolled, and then the schedule table need only have the class instantiation (which doesn't yet make sense without a timestamp; I don't get it), the instructor, and the time slot 'cuz the location (maybe), the type, and the clients in it are set in the class table. First, I wonder if I successfully followed you :-) Second, though, I don't get how I can have some clients in a class table when the class hasn't been assigned a time slot; how can the clients avoid collisions? % % Now, you can query the database and see how many time slots have more than % one class. % You no longer need to worry about double booking. Because I can come back to an unique index, you mean, perhaps? % % Hope this helps It's a start; thanks a bunch! HAND HNY :-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://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+EykaGb7uCXufRwARAi6CAJ9ACpgYWfWhHSaaLRBitc5bHL7tZgCfceL9 KTGOGwF4KfWPUNVSrLcYlEw= =+giV -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
Re: join help: i am lost
David, Just some thoughts.. See my comments below... A scheduling, or a booking, eventually has to have a class type (private or one of many groups -- so I suppose I could simply make a group class type 'private' and that type has only one slot), an instructor, a place, a time slot, and the client or clients to go in it. Sounds like you are on the right track. Looking at your first sentence below, you state that there are various types of groups classes, so why not have a type of 'private'. If I get you right, I'd have a class table pulling together the type of class (one of the typical group classes or this new 'private' one) and, somehow, the client(s) enrolled, and then the schedule table need only have the class instantiation (which doesn't yet make sense without a timestamp; This is unclear, what do you mean by timestamp...do you mean it has no time scheduled? From a technical standpoint, this is not an issue, but from a business standpoint, you would have people enrolled in a class that has not been scheduled. (Can happen but awkward...) First, I wonder if I successfully followed you :-) Second, though, I don't get how I can have some clients in a class table when the class hasn't been assigned a time slot; how can the clients avoid collisions? This is unclear collisions?... Are you asking how the clients would avoid double booking themselves? If that is the question, I think your business process has to control that i.e. you need to schedule class timeslots. Or, as the classes get scheduled, you would have to notify your clients about the schedule. Wouldn't you have to do that any way, since they would not know when the classes are supposed to be? I % Now, you can query the database and see how many time slots have more than % one class. % You no longer need to worry about double booking. Because I can come back to an unique index, you mean, perhaps? You run a query that counts the number of classes that are booked for each location at each timeslot and if the count is greater than 1, you have a problem. (simple SQL query would give you this) Just to re-itereate, I think you want to enroll groups..! not clients..! A client can be a group of one. This allows you to enroll groups of one or many. I am assuming that your clients may be, for examplle, John Smith or local community center(?) Also, if it was me, I would probably create a table for time slots. The way you have it structured, it works but it is not as flexible. Your finally scheduling table would have pointers to places, timeslots, personnel, groups, classes - 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: join help: i am lost
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Frank -- ...and then Frank Peavy said... % % David, % Just some thoughts.. % See my comments below... Thanks! % % A scheduling, or a booking, eventually has to have a class type (private % or one of many groups -- so I suppose I could simply make a group class % type 'private' and that type has only one slot), an instructor, a place, % a time slot, and the client or clients to go in it. % Sounds like you are on the right track. Looking at your first sentence % below, you state % that there are various types of groups classes, so why not have a type of % 'private'. Yeah. In fact, I have that now, though it hasn't gotten me far. % % If I get you right, I'd have a class table pulling together the type of % class (one of the typical group classes or this new 'private' one) and, % somehow, the client(s) enrolled, and then the schedule table need only % have the class instantiation (which doesn't yet make sense without a % timestamp; % This is unclear, what do you mean by timestamp...do you mean it has % no time scheduled? From a technical standpoint, this is not an issue, but % from a business standpoint, you would have people enrolled in a class % that has not been scheduled. (Can happen but awkward...) Right, and it certainly would be awkward. It seems to me that you have to have the instructor, the class type, the place, and the time before you can offer it to clients. So that was my worry. % % First, I wonder if I successfully followed you :-) Second, though, I % don't get how I can have some clients in a class table when the class % hasn't been assigned a time slot; how can the clients avoid collisions? % This is unclear collisions?... Are you asking how the clients would avoid % double booking themselves? If that is the question, I think your business Exactly; see above. % process has to control that i.e. you need to schedule class timeslots. OK. I like that. % Or, as the classes get scheduled, you would have to notify your clients % about the schedule. Wouldn't you have to do that any way, since they would % not know when the classes are supposed to be? Right -- but that's not a good approach, IMHO. % % % Now, you can query the database and see how many time slots have more % than % % one class. % % You no longer need to worry about double booking. % % Because I can come back to an unique index, you mean, perhaps? % You run a query that counts the number of classes that are booked for each % location at each timeslot and if the count is greater than 1, you have a % problem. (simple SQL query would give you this) OK. Yeah, I figured I'd have to do that on my own. I also want to make sure someone doesn't try to sign up a client in two spots at the same time, and will have to do that on my own. No biggie. % % Just to re-itereate, I think you want to enroll groups..! not clients..! % A client can be a group of one. Hmmm... OK; I can see that... % This allows you to enroll groups of one or many. % I am assuming that your clients may be, for examplle, John Smith or local % community center(?) John Smith, or maybe six or seven people taking a group class. % % Also, if it was me, I would probably create a table for time slots. The way % you have it structured, it works but it is not as flexible. At the very least, it stores a lot of redundant data; you're probably right. Then I don't have to worry about how to define the time slots (are we open from 6a - 7p today or do we close at noon or whatever?) to ensure that the classes get booked only at appropriate times. So this part looks easy and helpful. Hmmm... I'll wait until later to figure out whether to stick with one field or split into day and time -- and then fret about all of those duplicated day values for each hour ;-) % % Your finally scheduling table would have pointers to places, timeslots, % personnel, groups, classes OK. Here's where I have more questions, then. The class type will say what kind it is and how many slots it can have, so I know what kind of 'group' can go in here. Are you suggesting that I should have a table defining a group for each instance? Basically every time you have an instructor doing something, you have a corresponding entry in another table listing who's being taught? That seems just a little *too* redundant, and maybe a little less so if I reused groups (every client would automatically become a group of one, to be reused for any private instruction, and then there would be other groups as people came together in a group class). Even so, that still doesn't answer the question of how to have data of different magnitude in the same table. If I have one class with one person and another with two people, how would I have a single record for each which lists the client(s)? Thanks again HAND HNY mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL
Re: join help: i am lost
Even so, that still doesn't answer the question of how to have data of different magnitude in the same table. If I have one class with one person and another with two people, how would I have a single record for each which lists the client(s)? Easy, Your scheduling query results, as I said: places, timeslots, personnel, groups, classes 5th floor room, 1 to 2, BestInstructor, Local community center, yoga (group lesson) 5th floor room, 2 to 3, BestInstructor, Jon Smith group, yoga (private) 5th floor room, 5 to 6, BestInstructor, Open Group, yoga (open) In order to find out if Jon is scheduled twice, you would need to know if he is part of the Local community center group. Any way You may have to re-think parts of your database structure. A good book on ERD diagrams might help. Best of luck. - 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: join help: i am lost
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Frank -- ...and then Frank Peavy said... % % Even so, that still doesn't answer the question of how to have data of % different magnitude in the same table. If I have one class with one % person and another with two people, how would I have a single record for % each which lists the client(s)? % % Easy, So you say ;-) % % Your scheduling query results, as I said: % % places, timeslots, personnel, groups, classes % % 5th floor room, 1 to 2, BestInstructor, Local community center, yoga (group % lesson) % 5th floor room, 2 to 3, BestInstructor, Jon Smith group, yoga (private) % 5th floor room, 5 to 6, BestInstructor, Open Group, yoga (open) % % In order to find out if Jon is scheduled twice, you would need to know if % he is part of the Local community center group. Ahhh... No, it doesn't work that way; a group class is whoever signs up for that time slot that day. Think even non-appointment signin. Well, that may be what you're thinking with the open group. We need to track who attends, though. Even if we did use this approach, how would we track the group membership? Either we look at the client record and note of which groups Jon is a member, from zero to N, or (probably better) we have a groups table listing the groups and who is a member -- and we're once again back to how to list anything from 1 to N members. Do I just define up to N fields per row and fill in the left-most empty one? Do I stick the IDs in a text field and then parse my way across that field, or can the DB do it? % % Any way % You may have to re-think parts of your database structure. A good book on % ERD diagrams might help. Indeed. % % Best of luck. Thanks again HNY :-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://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+E1XCGb7uCXufRwARAm1cAKDkppWDoxVj86a12k8uDZaYejiK3ACgrX6d BoUc+3g7mN6Oysyl3ul7Sh4= =QLVn -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
