July 28, 2022 at 10:38 pm
I am tracking users and user interests that a user chooses. As in a user_id will pick all their interests(golf, tennis, bowling...)
User(user_id, attribute1, attribute2...)
User_interest(user_interest_id(sk), user_id, interest_id, attribute1)
Interest(interest_id, interest_description)
So User to User_interest is One to Zero or Many.
Interest to User_interest is One to Many.
I seem to sometimes get confused going the other way...
So is User_interest to User One to One? Or Many to zero or One?. It's not always the reciprocal relationship when going the opposite way is it? Meaning, if user -> user_interest is 0/1 to many, the other way (user_interest -> user) is always many to 0/1.
And User_interest to Interest is one to one also?
Thanks,
Jason
Microsoft Certified Professional: MCSE Data Management and Analytics
July 29, 2022 at 7:43 pm
Try not to look at the problem formally, mechanically. Describe what is happening in aa few sentences then look at them. This is how:
2. user_id will pick all their interests from some list of available interests (golf, tennis, bowling...).
Let's rewrite the two sentences:. First note nouns from the two sentences users , user interests, interests . Nouns will become entities in your database. One user can choose many interests, and we record that in entity user interests. Other users chooses their interests. Same interest may be chosen by several users. This gives us the following diagram
[Users] ----choose --<[User Interests] >---from----[Pool of users]
Both relationships (----<) are one to many, and that is not going to change. The question of cardinality depends on business rules.
If there is a business rule that says "Each user must have at least one interest" which is the same as "Users without interests are not allowed in database" then relationship Users to User Interests is 1 to 1 or many. If there is no business rule requesting at least one interest per user relationship would be 1 to zero or many.
Entity [Pool of interests] is not likely to request that each Interest from Pool of Interests appears at least once in [Users Interests], so relationship between [Pool of Interests] and [User Interests] would be 1 to zero or many.
Relationship 1 to 1 or many is OK in theory. However, inn todays database management systems it is not possible to enforce 1 to 1 or many in a declarative way. There is no constraint to enforce such rule, so we must write code somewhere. Front end is a good place to enforce such rule.
If we have 1 to many, what about 1 to zero.? This could literally mean that User must not have any Interests. That is illogical rule. If we want to track which user has which interests, such rule is contradictory to the database goal - to support tracking users and their interests.
Relationship 1 to 1 is actually always 1 to zero or 1. For example, we want to track user fees. There are no rules for fees, and it is secret peace of data. We will need a new entity, [User Fees], which allows only one row per user. In [Users] we have only one row per User, and fee cannot exist without user, so we have relationship [Users ] to [User Fees ]= one to one. Can we have 1 to zero or 1 in this case? Again, in theory it is a valid request. In practice, it is difficult to enforce.
Zidar's Theorem: The best code is no code at all...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply