August 27, 2002 at 11:09 am
Hi,I am having a database design despute with a dear friend and would greatly appreciate help in resolving it.The database in question needs to track "entitlement" allocation to users. An "entitlement" can be allocated to many users and a user can have many entitlements. An entitlement will have a maximum number of allocations, which could be anything from 1 - 50. Three tables exist to store this info:tbl_userstbl_entitlementsand tbl_userEntitlements.I am proposing a solution where each time an entitlement is allocated to a user, an entry is made in the tbl_userEntitlements. My friend on the other hand maintains that when an entitlement is created the tbl_userEntitlements table should be populated with all available instances of it, with the userID set to NULL. Eg. If entitlement A has a max of 10 allocations, 10 rows should be entered in tbl_userEntitlement with the foreign key userID being NULL. When an actual allocation is made the userID field will be filled with the right value. Is this solution denormalised and the reason for this a repeating group (entitlementID, UserID=NULL)?Many thanks
August 27, 2002 at 4:32 pm
Yes, that solutions is denormalized for the reason you stated. Basically if a lot of data can repeat with NULLs then normalize further. Unless for performance reasons with doing the join you have to store this way.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply