SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jul 2010
    Location
    Belgium
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question One unique field inside lookup table (both fields as primary key)

    Hi,

    First of all, i'm sorry if this is a stupid question, i'm kinda new at this

    I'm working on a database, part of which looks like this:

    USER
    id
    name
    email
    password
    moddate
    sectionid

    useraddress = lookup table, primary key
    userid (=user.id)
    addressid

    address
    id (=useraddress.addressid)
    street
    ....

    I'm making a script that lets the user modify/add data in these 3 tables using one form, using mysqli_insert_id to insert the id generated for the user table in the useraddress.userid field. Once user.id has been inserted into userid, the table generates the same addressid for every entry because the combination of data in both fields is the primary key. Is there a way to somehow make the data in addressid unique as well? Is a lookup table the way to go in this case ?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,903
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    I'm not sure why you have the useraddress table. That table makes the relation between user and address N:M. That is to say a user can have multiple addresses (which is correct), but also that the same address can be held by multiple users.
    The last bit also looks correct but has severe drawbacks. Assume I have a roommate and we have the same address, so we have one address.id in the address table and suppose I move. In this database that would my mean my roommate moves with me (since we still have the same address.id), but that doesn't have to be the case.
    I would seriously consider dropping the useraddress table and make the relation between user and address 1:N
    Unless you have a good reason to want a N:M relation of course
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Member
    Join Date
    Jul 2010
    Location
    Belgium
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're absolutely right. I'm making this database for a youth organization website. They tend to include lots of people that are siblings. The thinking behind it was that a user could have multiple addresses and that users could also share an address. So if one of the siblings comes of age and decides to move out i'll run into the problem you pointed out. I knew something didn't feel quite right with this database

    Thanks a lot for the help!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    my standard question for this type of schema is: do you care about an address (i.e. will you keep track of it) even if nobody lives there?

    apps which do care are ones like the post office (which needs to know whether an address is legitimate)

    if the address is important to you regardless of whether anybody lives there, then you need to keep track of addresses as separate entities

    otherwise, address is an attribute of some other entity, and should be carried as a simple data column (or columns, since addresses usually have several parts)

    another worthwhile analogy is this: when you store a table of people, do you set up a separate firstnames table to log all the different first names that people have? and link them with a foreign key?

    i've seen many people overcomplicate their databases with separate address tables when clearly there is no need to

    just because two people might have the same address doesn't constitute a need for an address table any more than two people called John require setting up a firstnames table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jul 2010
    Location
    Belgium
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will not keep track of the addresses once the user moves out, however the addresses will be used to send invitations. I thought keeping the addresses separate would help avoiding sending the same invitation multiple times to the same address, but maybe it would be better to filter out the duplicates during output.
    I guess I'm still not sure whether the address is an entity or an attribute in this particular case.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by da_deef View Post
    I thought keeping the addresses separate would help avoiding sending the same invitation multiple times to the same address
    so all your invitations are sent to "Dear Resident(s)" ??

    if i happen to live in a house with five other people, and you send only one invitation, who is the invitation for?

    and if you do put a name on it, which one?

    people sharing addresses in a typical database represent what, maybe 2% of all people? so you're saving how much on your budget for stamps, with an increased risk that not everyone will get one?

    seems like you are way over-complicating it

    put the address columns into the users table, and send an invite to everyone
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jul 2010
    Location
    Belgium
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're right, and I guess there's also no reason to keep track of multiple addresses here because i only need the one where they'd rather have their mail sent to.
    Thanks for clearing this mess up!


Tags for this Thread

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
  •