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!
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!
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!)
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.
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.
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.