Which database structure should you choose?
I want to implement a notification system. I have users and each user has a notification setting.
Structure 1:
Users Notification_settings Notifications
-id (pk) -id -id (pk)
-username -user_id (fk) references Users -user_id (fk)
-password -receive_email (boolean) -message
-is_read
Structure 2:
Users Notifications_settings
-id (pk) -id (pk)
-setting_id (fk) references Notifications_settings -receive_email
-username
-password
Notifications
-id (pk)
-user_id (fk)
-message
-is_read
Which database structure to choose or any other database structure for the notification system?
source to share
It seems like it should be users <notifications (one to many), but perhaps you have a specific reason that it should be 1-1. In this case, I would make the parent table (the one that does not contain the FK column) in the table with a one-to-many relationship. Therefore, it would naturally be advisable to use storing the user ID in the notification table.
It would seem that the notification will always have a user, but NOT the other way around. Therefore, you should store the foreign key in UserID in the notification table and not vice versa.
edit-- as others have suggested, if you really want a 1-1 relationship, you can simply add notification fields to the User table. They seem to violate the normalization rules at first glance, but if this reeeaaaally is a 1-1 relationship, then by all means it has
Edit 2- Since you have explicitly stated that a notification does not exist without a user, I will definitively say that you should store the foreign key for the user in the notification table, no exceptions (except when you want to store notification information inside the user table :)
Edit # 2937:
You have to store your custom notification settings in the User table - no need to split this into another table unless you have some confusing construction and have 256 columns for your user already and that's the limit.
You should keep notifications in a table separately , with a One-Many relationship from users to notifications. This is my last answer, Regis :)
source to share
And now for a quote from Joe Celko :
A strong entity is one that exists by itself. A weak entity is one that exists because of a strong entity. A classic example is a customer order; the order header is strong and the order details are weak. If an order is omitted, all the details of the order should disappear.
So, can a User exist on it without notice? Then the notification table must have a foreign key for the Accounts. Is it true? Then do it differently. Is not it? Then maybe your model is wrong and there must be a join table between them (even with unique constraints on it), or maybe they really belong in the same table. I don't like this last option of placing them in the same table, because of course you have already come up with two nouns to describe different objects.
Can objects other than Users have a "notification"? Perhaps thinking this way can help you model this domain.
Refresh . Additional ideas:
If you were to put all these columns in one table, which one, if any, now looks like it would contain redundant data? Imagine that there are only a few different messages. You might not want a notification table, but a message table and a table of connections between them and users that can store messages sent to users over time, including if they were read or not. The receive_email function might be the best attribute for Users at the moment, although perhaps just having a message associated with a user is enough to tell that User to receive email. These are just a few of the things I could think of, and I hope they help to better understand the application.
Also, beware that the bit / boolean datatype is not ANSI SQL, can often be obtained from other data, or may even turn into int down the path to multiple states.
source to share
Unless your site is huge in traffic, you put it all in the same table (user and settings). This is the correct answer for the OP. Usually, split 1: 1 across different tables when multiple conditions occur (together):
- each group of fields refers to a different module in your application.
- each group of fields has access at different rates (username / password for each login, payment settings once / twice a week, for example)
- There is huge traffic to your site, where you need to milk any winnings from your system.
As you can see from above, most do not need to separate it.
source to share