SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2000
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have two tables:

    WORKSHOP
    ----------
    Workshop_ID (auto_incrementing field)
    Name
    Description
    Date_Started
    Total_Members
    Admin_Name
    Admin_ID

    WORKSHOP_MEMBER
    -------------------
    Wk_Memb_ID (auto_incrementing field)
    User_ID
    Workshop_ID
    Member_Name
    Administrator (1 if yes, 0 if not)

    Here's how I have it set-up: There's a maximum of 10 members per workshop. A site user can fill in the appropriate form to create a new workshop. After the user gets done creating the workshop, two entries in two separate tables are added (see above), one is for the new WORKSHOP, and the other is the WORKSHOP_MEMBER. The person who created the workshop is designated the Administrator (1). Anyone who joins after him/her is just a regular workshop member and is added to the WORKSHOP_MEMBER table, with a 0 in the Administrator field.

    Now here is where it gets tricky... If the administrator leaves the group, I want the next person who joined to become the admin. How should I set this up? Can I go by the highest Wk_Memb_ID && Workshop_ID? or is there another way?


    Thanks in advance,
    Sadia

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would probably best to have a date field in the member table like date_joined or something. Then you can just go to the next oldest date and make him the administrator.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, you have all of the info already in the table that you need: the Wk_Memb_ID.

    Run these queries after the admin is deleted from the WORKSHOP_MEMBER table and you have the Workshop ID:

    Code:
    SELECT min( Wk_Memb_ID ) 
    FROM   WORKSHOP_MEMBER
    WHERE  Workshop_ID = $wkgrp_id
    Store that in a variable. My example uses "$wk_memb_id".

    Code:
    UPDATE WORKSHOP_MEMBER
       SET Administrator = 1
     WHERE Wk_Memb_ID = $wk_memb_id
    <Edited by MattR on 02-04-2001 at 12:27 AM>

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The auto-incrementing column should only be used as a reference to the record where it sits. You should never rely on it for ranking, counting number of records, or anything else other than referring to the record where it sits. A date field will allow you to do all sorts of things and no matter how many deletions or updates are made the date field will always refer to the date joined.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    The auto-incrementing column should only be used as a reference to the record where it sits. You should never rely on it for ranking, counting number of records, or anything else other than referring to the record where it sits. A date field will allow you to do all sorts of things and no matter how many deletions or updates are made the date field will always refer to the date joined.
    Huh? Why?

    Example:
    Bob signs up a new workshop, and he is added as the admin.
    A short listing of the tables shows:
    WORKSHOP
    --------
    1, "Bob's Workshop"....

    WORKSHOP_MEMBER
    1, somenumber, 1, "Bob"

    3 people sign up, 2 are on his workshop.
    We have:

    WORKSHOP_MEMBER
    1, somenumber, 1, "Bob"
    2, somenumber, 1, "Joe"
    3, somenumber, 2, "Alf"
    4, somenumber, 1, "Jane"

    I can update everything in that table, but the auto-increment IDs will remain the same, and if Bob quits I will still be able to recognize that Joe is the next in line. If I delete a record, say "Joe", here is what I have:

    WORKSHOP_MEMBER
    1, somenumber, 1, "Bob"
    3, somenumber, 2, "Alf"
    4, somenumber, 1, "Jane"

    If I delete "Bob" next, then again, the correct user, "Jane" will become the admin.

    The only issue that pops up is if, in the 4 person table, the administrator accidentally deletes "Joe" and then re-adds him. In that case we have:
    WORKSHOP_MEMBER
    1, somenumber, 1, "Bob"
    3, somenumber, 2, "Alf"
    4, somenumber, 1, "Jane"
    5, somenumber, 1, "Joe"

    And "Jane" would become the admin on "Bob"s departure. At this point, the site admin would have to step in and change Joe to the admin and set Jane to 0, if it was really needed. Seems like a user training issue rather than any sort of a deficiency in the structure of the table / use of auto number fields. The autonumber field guarantees that a record entered BEFORE another one will have a lower number, right? So, other than the slight annoyance of the “Ooops I deleted Joe, let me re-add him.” Situation it seems perfectly acceptable to use what is currently in the table. Why add another column when it is not necessary?

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Exactly the database is not normalized, What if the site owner decides that he wants to make Alf the administrator once Alf leaves Jane is the new leader when techincally Joes should be the next leader ater Bob. In this example it is not as prevelant but believe me the correct way to ahieve this without glitches, like you siad the odd issue, well doing things the right way first alleviate the chance for the issues to arise, would be a date field.

    WORKSHOP
    --------
    1, "Bob's Workshop"....

    WORKSHOP_MEMBER
    1, somenumber, 1, "Bob"

    3 people sign up, 2 are on his workshop.
    We have:

    WORKSHOP_MEMBER
    1, somenumber, 1, "Bob"
    2, somenumber, 1, "Joe"
    3, somenumber, 2, "Alf"
    4, somenumber, 1, "Jane"
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    Exactly the database is not normalized, What if the site owner decides that he wants to make Alf the administrator once Alf leaves Jane is the new leader when techincally Joes should be the next leader ater Bob. In this example it is not as prevelant but believe me the correct way to ahieve this without glitches, like you siad the odd issue, well doing things the right way first alleviate the chance for the issues to arise, would be a date field.
    It all comes down to what will his application do? What tasks will it be expected to perform? If, on the odd chance, he will be reassigning Admins from the person who created it to one who signed up, I could start to see the necessity of adding a datetime field. However, if the author's involvement is slim to none, the overhead of a datetime field is pointless. I agree that the datetime field is usually the “right way”, but only if you need it. I guess it’s a non-issue for the small amount of rows he’s probably going to insert, so really he could stick 10 text fields in there and not decrease performance. However, if he plans on having large amounts of these rows, he might want to evaluate different ways of implementing a “who signed up first” marker.

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by kankohi

    WORKSHOP
    ----------
    Workshop_ID (auto_incrementing field)
    Name
    Description
    Date_Started
    Total_Members
    Admin_Name
    Admin_ID

    WORKSHOP_MEMBER
    -------------------
    Wk_Memb_ID (auto_incrementing field)
    User_ID
    Workshop_ID
    Member_Name
    Administrator (1 if yes, 0 if not)
    There are bigger issues with this schema than whether or not you use auto-increment to order the records. It doesn't pas the second normal form. These are my concers:

    Is there another table that holds just the member details, or is the only place where member details are stored are in the WORKSHOP_MEMBER table?

    IF MEMBER table exists THEN
    Do not store multiple fields of information about a member in the WORKWHOP_MEMBER table such as User_ID and Member_Name. Just store the User_ID in this table as the foreign key. You can find other details about a member such as their name from the MEMBER table. This eliminates data redundancy and update anomolies.
    ELSE
    If you don't have a seperate MEMBER table and you are using WORKSHOP_MEMBER to store member info then your schema assumes that a member can belong to none or one workshops. You could create multiple rows for the same meber if they belong to more than one workshop but this is fraught with problems. If a member can belong to many workshops then you need a seperate MEMBER table as I described above.
    END IF

    Again you have redundant data in the WORKSHOP table. You just need the Admin_ID. You can lookup the Admin_name from the WORKSHOP_MEMBER table. Also, if you store who the admin is in the WORKSHOP table then you don't need the boolean admininistator field in the WORKSHOP_MEMBER table.

    You don't even need the total members field in WORKSHOP as this query will always tell you the same thing:

    SELECT COUNT(*) FROM WORKSHOP_MEMBERS WHERE Workshop_ID = 'someValue';

    Can I suggest that you need to read a book on data normalisation. I know that Kevin Yank has written about it in his PHP/MySQL tutorials at http://www.webmasterbase.com Anybody who wishes to use a relational database and sql *must* understand data normalisation to the third normal form. There is no way around this. Sorry if I sound terse. Please let me know if I can give more help.

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good post there freakysid. I would think that there is some sort of a members table since those two tables contain no member information other than the name – no e-mail, password, etc. so it would make it difficult to perform any sort of authentication without it. Good catch on the admin ID part, I didn’t see that until you pointed it out!

    If he implements your suggestions I think that would pretty much eliminate all of the badthings that plague the current design. There are still issues that would keep it out of 4NF, but really who cares at that point. I try and keep all of my relations in BCNF and denormalize when I have to for performance issues.

    Did you learn the normal forms before you started relational design? I found the relational model so intuitive (vs. say the OO model or the evil Relational Algebra model) that I was designing “good” relations (3NF or BCNF) from the get-go, only later in my studies did I come across a formal method in decomposing relations into “better” ones.

    I’ll echo your comments and say that learning database normalization (up to 3NF or BCNF) is strongly recommended for any serious database application developer (mandatory for any aspiring DBAs as well!). You can probably get by with a limited understanding of normalization (e.g. “Ok, I don’t want to duplicate data, so I’ll drop this field and put it in this table..”) but in the long run knowing through the third normal form will help you design good databases.

    On an off-topic note, I’m eager to see the database schema changes the developers made to VB 2.0’s backend. As a DBA I eat that stuff up! I also wanna take a look at their code as well to see the performance optimizations. Good things come to those who wait, eh? (unless anyone is waiting for me to do a 2.0 Sybase port. The longer it takes to get the code the longer it will take to port it! )

  10. #10
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LOL - I really don't understand the theory of normalisation very well! - functional dependencies, transitive dependencies, etc - I'm pretty shaky with my maths

    To be honest, I use intuition rather than formal methods to get to 3NF and I don't think I understand Boyce-Codd Normal Form (BCNF) well enough that I would be able to pick when it is being violated.

    As for the realational algebra - I was taught it. However, I don't know that it was that useful in learning realational database design! Again, if you struggle with the maths and set theory like I do then it gets in the way of learning database design at a practical level and stuff like sql. I think well explained examples are always the best way to learn, such as tables that show what the data "looks like" after an inner or outer join, etc. Realational algebra just keeps the academics in a job!

    I read the text book "Fundamentals of Database Systems" by Elmasri & Navathe - and it is a very theoretical book that I find hard to understand. I hope there are more practical books out their that teach relational data modelling!

    I program mostly using OO - and as you suggest that is its own nightmare when it comes to object to relational mapping. It also means that a database schema I design for a OO design is quite likely going to break some of the normalisation rules But boy this is where I have a lot to learn.

    I think another trap for ppl like myself (who don't have much database experience and are only using databases on a very small scale as a "back-end" data repository for their applications) is that we don't use anywhere near the functionality and power that sql (in its many different proprietory flavours) offers - its almost a programming language in itself. Instead we stick to INSERT, DELETE and UPDATE statements. Also, unnecessarily pulling more data than we need out of the database and processing it in our program code when we could be using the RDBMS to do this processing through our SQL. Its too easy to write SELECT * FROM TableName;

  11. #11
    SitePoint Enthusiast
    Join Date
    Jan 2000
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Matt and freakysid,

    I'm sure you guys have deduced by now that I am a novice at this

    And to let you know, I do have a separate table for MEMBER info... but here's my question, if say for listing the workshop info (30 plus workshops) I have to query three tables (WORKSHOP, WORKSHOP_MEMBER, and MEMBER) would that slow down the process? would it be faster if I had the needed info in two tables (WORKSHOP and WORKSHOP_MEMBER)?

    and back to my original question... so using wk_memb_id and workshop_id would work? I also would like the program to automatically take care of who becomes the next admin, but if I ever have to change it manually, how do I re-automate the process... If member A, B, C, D sign up in that order, and A leaves the group but I want member C to become Admin and then after that leave the order as it is, how do I do that? freakysid, I didn't really understand what you meant when you said do it by dates, wouldnt it generally be the same thing using wk_memb_id?


    sorry to sound like such a newbie,
    Sadia

  12. #12
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That was freddydoesphp that told you to go by dates, not me.

    Two tables faster than three - the short answer to your question is no, the long answer has to do with indexing which you need to worry about if your database becomes big and your queries start to slow down. The integrity of your data is the paramount concern thus my harping about removing redundant data.

    I'm going to assume that you delete the member from WORKSHOP_MEMBER when they leave a workshop. Also, I'm going to assume that you take my advice and store just the Admin_ID in table WORKSHOP and get rid of the unnecessary "Administrator" field from WORKSHOP_MEMBER. Also, I'll assume that you will use the auto-increment field Wk_Memb_ID to keep track of the order in which ppl join a workshop.

    OK now for the fun...

    Assuming that when the "administrator" leaves the group their record is deleted from WORKSHOP_MEMBER, to find the next person who joined the group...
    Code:
    UPDATE WORKSHOP
    SET Admin_ID = (SELECT User_ID 
                             FROM WORKSHOP_MEMBER
                             WHERE Wk_Memb_ID = (SELECT MIN(Wk_Memb_ID)
                                                                 FROM WORKSHOP_MEMBER
                                                                WHERE Workshop_ID = $id))
    WHERE Workshop_ID = $id
    Freaky eh? You can see that our SQL query is actually a query with two queries nested into it. Looking at each one in isolation:

    QUERY_1

    SELECT MIN(Wk_Memb_ID)
    FROM WORKSHOP_MEMBER
    WHERE Workshop_ID = $id

    This returns the lowest value of Wk_Memb_ID for rows that contain the Workshop_ID with the value $id; ie, who should be the next administrator.

    QUERY_2

    SELECT User_ID
    FROM WORKSHOP_MEMBER
    WHERE Wk_Memb_ID = QUERY_1

    This finds the User_ID of the person who we identified should be the next admin in QUERY_1

    QUERY_3

    UPDATE WORKSHOP
    SET Admin_ID = QUERY_2
    WHERE Workshop_ID = $id

    This updates the Admin_ID field for the relevent record in WORKSHOP to contain the id of the next admin which was identified in QUERY_2.

    Finally, don't apologise for being a newbie. We are all newbies at something or another - otherwise none of us would be lurking around these forums looking to learn.
    <Edited by freakysid on 02-04-2001 at 11:48 PM>

  13. #13
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry freakysid MySQL does not support sub-queries, as far as I know. I still stand firmly against using auto-incremeneting columns for keep track of time or number of records in a table, it should primarily be used to refernece a particular row of data. So this brings upon another question how to the update, I am afraid you will need to use two queries. I would suggest again putting a date field in the member's table and like freakysid said put an adminid in the workshop table that corresponds with the user id in the member table. So on to the two queries.

    $result = mysql_query("SELECT Workshop_ID, User_ID, min(date_joined) as newadmindate from WORKSHOP_MEMBER GROUP by Workshop_ID");
    while($row = mysql_fetch_array($result)) {
    $adminids[$row["Workshop_id"]] = $row["User_ID"];
    }

    foreach($adminids as $adminid => $userid) {
    $result = mysql_query("UPDATE WORKSHOP set adminid = $userid WHERE Workshop_ID = $adminid");
    }
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  14. #14
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh bummer about nested queries in mysql - I haven't tried to use them 'cause I have almost no experience in using mysql. Anyway, as you suggest you can just spit it up into seperate queries. As Phillip Greenspun (of MIT) says "ANSI SQL92 would be a standard if anybody followed it"

    LOL about the auto_increment arguement. I still don't understand your issue with it - but each to their own! The only problems I see with it are that one day you will run out of values (what's the max value of a long unsigned int?) and that you should keep in mind that the values will not necessarily be at equal arithmetic distance (ie, not 1,2,3,4,5 ...) but could be (1,2,5,9). However, if you can tell me that the values may be out of sequential order (1, 2 , 9, 5) then I'm worried, if not then its more an arguement of style or general design heuristics over what will or will not work.
    <Edited by freakysid on 02-05-2001 at 12:45 AM>

  15. #15
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes,

    MySQL doesn't support the often-used subquery "feature" of SQL92. I assume that was a decision to eliminate complexity in the query parser / query optimizer, and honestly isn't surprising considering the role of MySQL in the world. Strip out anything unnecessary for speed!

    But when you think about it more, the datetime field does seem like a good idea if the author wants something slightly more flexible than the auto_increment field. Again, I'd say not to use it if there will be no goofy situations, but it seems like it *could* be a hassle (if he's manually removing the admins, chances are he could manually set one anyway -- if you read his user A, B, C, D thing he doesn't want it to automagically select B, but I think he needs to sit down and really think about what his application wants to do and then formulate some exceptions like the above and then write code accordingly).

    Also, of course the more tables you bring into a join will slow it down -- if I bring 10 tables into a join vs. 2, there will definitely be a performance hit. However, if you have a 30 row table (workshops) joined to a 300 row table (workshop members) joined to a 3000 row table (users), you're still way below what the database would consider life-threatening.

    I’m confused on the comment though “I still stand firmly against using auto-incremeneting columns for keep track of time or number of records in a table,”. I’m not sure what you mean by that… Using:
    Code:
    SELECT COUNT( some_id )
    FROM   some_table
    is a perfectly acceptable way to find the number of rows in a given table. SELECT COUNT( * ) works fine as well. The only way I could think people would misuse it is to say: SELECT MAX( someid ) and then use the result as the number of rows. That would be an invalid assumption, yes.

    [rant]
    Speaking of auto_incrementing IDs, I’ve found they are WAY overused in the majority of the applications I see. For instance take a look at the old (pre 2.0) VB session table. You have:
    session ( sessionid, userid, ipaddress, lastactivity, location )

    There’s no reason for the sessionid since the application forces the ipaddress field to be the primary key. If the app forces a primary key even though the table does not, then that’s just bad table design.

    Redesigning it to be something like:
    session ( ipaddress, userid, lastactivity, location )

    would follow the specifications of the application (and allow multiple users on one IP, say behind a firewall, to have distinct sessions), and then INSERT statements would be less costly (in terms of speed and CPU usage) since you don’t have to ask the database to find the next incremental value.
    The very minor speed decrease in queries using the char field to search is offset by the speed gains in not having to worry about the sessionid being calculated every time. Plus, you don’t have to worry about the field eventually overflowing (not sure what happens in MySQL when you overflow a field… I wonder if it pukes?).
    [/rant]

    Just some thoughts before I turn in to bed. No, not A bed!

  16. #16
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Matt you basically supported my point exactly, if an auto_incrementing id can and should NOT be used for counting, by that I mean never should someone say "oh my highest id is 745 so I must have 745 records in my table" because this is most defintely not going to be accurate, nor should someone do SELECT max(id) from tablename like you showed above, along those lines noone should use the id for ranking, rank should be determined by some other factor such as date. Especially if the table gets large and with the example given if you ended up with 100 workshops with 100 members each. If you were to start moving members around and deleting members, eventually your theory of the next id after the current Administrator will always be the next in line for date joined, which would be false. I really don't think its going to add to much performance wise to add a date field. Which would also make the application much more scalable.

    What if the owner wants to start tracking all members who have been in a workgroup for at least one year. Or who joined this year. Without some way of tracking the members through their entire membership with the site you would be stuck, you would need to have a date field in my mind. Just 2 cents.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  17. #17
    SitePoint Enthusiast
    Join Date
    Jan 2000
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all your help

    Matt, to answer your question, I want the program to handle Admin setup automatically. i.e. if A leaves, B automatically becomes admin, and when B leaves, C becomes Admin, and so on.

    But you guys raised an important point earlier, what if for some reason (say members C, D, and E dont want B to become Admins) I want to bypass B and give the admin to C. Could I leave the program as is? let it run and when C leaves the group the next in line would be admin?

    The way I see it is that if I ever do have to bypass someone I have two choices, I bump them to the end of the line (re-sign them up as the "newest" member to the workshop, thereby giving them the lowest ID of the group) or leave them there as is (with the highest ID) and they automatically become the next admin. Could that work?

    thanks,
    Sadia
    (she not he)

  18. #18
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Kankohi when you raise the point of sometimes you may want to bypass someone but not always, it will be hard to automate this. It would be easy to, again with a date field, change the status for the oldest existing member to administrator when a member is deleted, but if you want to start throwing in the ability for other members to boycott a certain member from becoming administrator and to have the program skip a member and give admin rights to the next member, you are moving in to a more in depth way looking at this.

    I would propose, that you simply set up some sort of admin interface with a master username and password that can override any of the settings set forth by the system, then when you delete a user you can simply reassign the admin rigths to whomever you want, let's say you creat a drop down list in thml that holds all the member names from a particular workshop. You could use something like this to select which user will get the admin rights, and if you didn't select anyone it would simply default to the oldest existing member.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •