Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2010
    Posts
    6

    Two way (dual) one-to-one relationship

    Hi,

    Im currently designing a database for a new client (my first!) and have arrived at a design where I require a two way one-to-one relationship. I never remember this in any of my studies, so I thought someone could confirm if this is indeed the correct relationship.

    I have two tables, Accounts & Identity.

    • The accounts table has accountId (pk), identityId (fk), password, email.
    • The Identity table has identityId (pk), accountId (fk), name, address, country, phone.


    The accounts table can only have one identity associated with it, but doesn't have to have one.
    The identity table can only have one account associated with it, but doesnt have to have one. (same as above)

    The identity table will be prepopulated with a list of identities. The application will require the user to enter the account details and then if requested to link the account details to the next available identity.

    By having a foreign key in the Identity table, I can very quickly find an available identity which has not been used.

    Is there any better way to model this relationship? If anything is unclear pls let me know and I will expand.

    Any advice is appreciated. Thank you for taking the time to review this question!

    Dominic

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Sounds right to me. Just remember to allow nulls in the foreign key columns.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    An account #222 has an entry in the identityId column of #555
    If I looked for identity #555 in the identity table, would I then see #222 in the accountId column?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MarkATrombley View Post
    Sounds right to me.
    Have another read. Sounds wrong to me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Quote Originally Posted by pootle flump View Post
    An account #222 has an entry in the identityId column of #555
    If I looked for identity #555 in the identity table, would I then see #222 in the accountId column?
    That seems to be his intent, otherwise it wouldn't make any sense.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MarkATrombley View Post
    That seems to be his intent, otherwise it wouldn't make any sense.
    Makes no sense that way.

    My reading is that these should be a single relation. The account sounds like login details for an identity to me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2010
    Posts
    6
    Quote Originally Posted by pootle flump View Post
    An account #222 has an entry in the identityId column of #555
    If I looked for identity #555 in the identity table, would I then see #222 in the accountId column?
    Hi pootle flump, yes that is correct. The application is for a client that runs an internet marketing business and needs to manage hundreds of accounts on behalf of his customers. He associates 'fake' identities with each account to maintain some sort of anonymity with respect to his company - if that makes sense. He has been using excel to maintain two worksheets, one with a list of user accounts and another with a list of pre-populated fake identities. Sometimes he doesnt need to associate an identity with an account.

    The client requires that when an account record is created, he can click a button and the next available identity is shown within the UI. If she doesnt like it, she can click again and it will select the next available identity. When she saves the account, the identity must be associated with the account.

    When the user is looking for an identity to associated with the account, I will search for the next null value AccountId in the Identity table.

    Is there a better way to go about this? I would love to get this right the first time as this is an important first time project for me!

    Thank you.

    Dominic

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just so I understand, the data in the Identity table is meaningless? Accounts are associated with nonsense data?

    I just want to get the right solution here - there is good reason that you never studied two way one-to-one relationships - there is no such thing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The reason I ask is if the answer is "Yes" then it would probably be easier just to generate identities on the fly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2010
    Posts
    6
    It is meaningless in the sense that it doesnt represent a real person. I suspect the reason for the identity data is if a company requires more than just basic user account details (username, password, email) for registration such as first name, last name, address etc. She may need to refer to this data when interfacing with the company etc. My first instinct was to put the data all in one table, but she pre-generates hundreds of fake indentities at a time using some tool.

    At the moment, all I have is a spreadsheet with two worksheets, accounts and identities. In the identities worksheet, she has created a column called 'account' where she puts the name of the account that she has associated the identity with. The are hundreds of empty records all over the place which I suspect is from her selectively choosing an identity for whatever reason.

    Does that make sense? Thank you pootle flump. (btw, I loved the flumps!)

    Dominic

  11. #11
    Join Date
    Jan 2010
    Posts
    6
    Part of the spec was a tool to import a list of identities in csv format. The accounts must be associated with these identities and not generated on the fly.

  12. #12
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    A more "proper" way to design this is to replace accountID in the Identity table with a isAssigned bit field that defaults to false on row creation. When you assign the identity set the flag and store the identityID in the account table. You can always find out which account an identity is assigned to by joining to the account table.

    While this design is more "proper" it accomplishes the same thing and the storage is almost the same (one byte to store the bit field versus four bytes to store the integer ID). Perhaps this will make the poodle happier.

  13. #13
    Join Date
    Jan 2010
    Posts
    6
    Mark, I didnt think of that, that does seem like a simpler way to do it!

    I just spoke with my client and she tells me that there is an api to the system she uses to generate her identities

    This means that I could combine the account and identities data in a single table and generate the identities if/when required. However she also tells me that she only stores an identity for roughly 1 in 10 records. This means that I would have alot of table columns with empty fields, bring me back to a two table design.

    whew, this is really interesting!

    Cheers again for the help.

  14. #14
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    The varchar datatype usually has an overhead of 4 bytes (depending on the database engine used) but only allocates the space actually used. So you can define 10 varchar columns and if they aren't used you have only "wasted" 40 bytes per row.

    But for a 1 in 10 return I would break it into two tables. Especially if you are loading identities in advance of creating an account.

Posting Permissions

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