SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Users and IDs and how Primary Keys may or may not relate to both...

    An application that I'm beginning to work on requires two different tables. The first table has "members" while the other has "users". Both have a field called "ID".

    MEMBER TABLE:
    ID -- VARIOUS FIELDS....

    USER TABLE:
    ID -- VARIOUS FIELDS...

    The ID in MEMBER TABLE is set to auto-increment. This is to support continual registration of various members, however, I'm not sure I need to set auto-increment to ID in USER TABLE because the two users in both are not entirely exclusive from each other. In other words, a USER can very well be a MEMBER.

    Should I just make 1 single MEMBER table and create fields inside it to represent user capacities or should I continue to create 2 separate tables?

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,807
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    That should be two tables. To save the same id in both tables you save in tha autoincrement one first then retrieve the last id to use in the insert to the second table.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Wolf_22 View Post
    Should I just make 1 single MEMBER table and create fields inside it to represent user capacities
    yes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Will there just be two types of user "member" and "user" or will there be other types as well?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This website / pseudo-application is going to be meant for a senate of an organization, so when I refer to "member", I'm actually referring to "senate member". I fear this probably helps in understanding what I'm trying to do here.

    So with that being said, I thought it would be preferable to have a table for "senate members" and then another for "users" in that the user is someone who manages the members--equivalent to your basic admin, etc. After thinking this over, I began to believe that I should just have 1 single table for everyone and simply include the respective fields for authentication or admin-like capabilities. It made me second-guess myself, though, because of the other fields that would be in this same table, such as "office phone number", "room number", "department", and other profile-like information. Is it okay to have that sort of information all in 1 table like this??? Does it break any pertinent MySQL laws?

    Sorry for the confusion, too, btw.


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
  •