| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2009-05-05 : 23:46:32
|
| Hi,I have an application with a "users" table. I am adding extra functionality to the application, and I need to add a place to store settings for each user.There are about 5 "privacy" settings for each user. I can either add these to the current column, or I can break out to a new table.Adding it to the current column is easier, but I am not sure if it would be better in a new table.My concern about a new table is, how do I make sure that every user in the users table automatically inserts a row into the new "privacy" table setting ? I'm just worried about syncronization. Currently I have just put a check in the SELECT statement that checks if the row is there, and if its not we insert a blank row ..I don't think this is good practice tho and its just unnecessary overhead..any direction on this is much appreciated !Thanks again! :)mike123 |
|
|
malaytech2008
Yak Posting Veteran
95 Posts |
Posted - 2009-05-06 : 01:31:29
|
| Hi,Depending on the scope of privacy settings. I think if user can have only one privacy setting then add one more column to the existing user table. and If user can have multiple privacy setting then creating new table for Privacy. create table with column having privacy settings.add one more column to track between the user table and privacy table.Set the value of 0 or 1 for choose the privacy setting by the user.You can set default setting initially. Later user can change it in his own.In your case- 1.if in user table records already exists in user table then insert new records in privacy table with userid and default privacy settings.2.Malay |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-05-06 : 01:39:29
|
quote:
1.if in user table records already exists in user table then insert new records in privacy table with userid and default privacy settings.
my question is basically this, how do I do the above properly and ensure no users are left behind ?for example, what if new users are inserted into the users table, during the time I do this new insert into the privacy table ?im worried about a few users getting left behind etc..thanks! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-06 : 02:07:29
|
You can have a insert trigger on the users table that inserts records in the privacy setting table with default values.Then if you feel the need to, you just have to update the privacy settings table.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-05-06 : 02:09:58
|
| Hey Peso,Ok I guess this is the best setup ? I guess if I apply the trigger first, then I can run a statement to ensure all accounts without a corresponding value in the table will have one.Much appreciated !Thanks,Mike123 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-06 : 02:13:36
|
The trigger will only add the corresponding privacy records after created, so yes, you will have to run a query to make sure all previously existing user do have a privacy record.INSERT Privacy ( UserID, ... )SELECT u.UserID, ...FROM Users AS uWHERE NOT EXISTS (SELECT * FROM Privacy AS p WHERE p.UserID = u.UserID)
E 12°55'05.63"N 56°04'39.26" |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-05-06 : 03:00:41
|
| Hey Peso,Awesome.. this sorts me out perfectly ! Thanks again,Mike123 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-06 : 03:50:59
|
Thank you for the feedback.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
help with db design / best practice