412 reputation
39
bio website modeltext.com
location Toronto, Canada
age 54
visits member for 4 years, 2 months
seen May 25 at 12:54

Jan
14
comment Effect of denormalizing
Given that I'm asking a sincere question and posting this as a possible answer, I hope it's obvious that if you think this answer is wrong, then I'd like you to tell me why/what is wrong about it instead of simply downvoting.
Jan
12
accepted Effect of denormalizing
Jan
9
answered Effect of denormalizing
Jan
9
revised Effect of denormalizing
try to clarify by adding a graphic-format version
Jan
4
awarded  Quorum
Jan
4
comment Effect of denormalizing
@user61852 What is unclear about this question and how can it be clarified?
Jan
4
comment Effect of denormalizing
@user61852 It's 1:m in the sense that each user appears once in the table, and each account appears many times. It's a m:1 relationship in the sense that each user has one account and each account has many users.
Jan
4
comment Effect of denormalizing
Thanks for the heads-up about primary key. That won't be a problem, because UserAccounts could have userId as primary key; but it could also have userId+accountId as primary key plus userId as a simple 'unique' constraint. Also it seems that (using MS SQL from 2012 onwards) A foreign key constraint does not have to be linked only to a primary key constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
Jan
4
comment Effect of denormalizing
Yes I'd prefer to put non-nullable accountId column in the 'Users' table (to define a required many-to-one relationship); but given that I cannot alter the Users table, I implement that relationship with a new 'UserAccounts' table.
Jan
4
comment Effect of denormalizing
I won't move users from one account to another (link). I certainly won't move Sales and other such rows from one account to another. I don't think I'll have update anomalies, a) because I have no updates, b) because I have a foreign key relationship (link).
Jan
4
comment Effect of denormalizing
Yes, that's the only hypothetical situation I was able to think of. I'm told it won't happen. If it did happen and if I'd done it the first way, that would make the change easy and cause problems: because old (historical) Sales rows (created when the user was in the old account) would seem to be reassigned to the new account when the user was moved to the new account. STaoring the accountId (with the userId) in the Sales table helps to make it clear that's not allowed: a row in the Sales table is to a user and to an account, and the account cannot change accidentally.
Jan
3
comment Effect of denormalizing
You said, The specific denormalization you suggest will bring you headaches. Can you please identify any potential problem/headache from this specific specific denormalization?
Jan
3
comment Effect of denormalizing
I don't see what you're trying to tell me. Wikipedia says something about 'update anomaly' but so far as I can see I'd have relational integrity by defining FOREIGN KEY (userId, accountId) REFERENCES UserAccount (userId, accountId)
Jan
3
awarded  Yearling
Jan
3
comment Effect of denormalizing
Playing devil's advocate, I might see userId+accountId as a single (composite or compound) key. Why does it "turn my data into rubbish" to do it that way: for what reason (in what use case or scenario) is it sub-optimal?
Jan
3
comment Effect of denormalizing
some old sage said once: "depend on views, not on tables". Is there any reason for applying that old adage in this case? Why is a view better than a table in this scenario? I'm aware that the tendency is to prefer normalization but I could not think of any good reason for doing it in this case, and I feared that applying the adage just because it's old would be an example of Cargo cult programming.
Jan
3
comment Effect of denormalizing
since the relationship between User and Account is a one-to-one relatioship It's many-to-one.
Jan
3
comment Effect of denormalizing
Ask yourself: What if a user changes account? I did ask that explicitly and I was told (by the customer/product manager) that will never happen. I suppose that if (unforeseen situation) a person must change account, they'll do that by becoming a new user in the new account (instead of moving the old user object to a new account).
Jan
3
comment Effect of denormalizing
If I had been permitted to modify the Users table, then I would have defined a non-nullable accountId column in the Users table (to define each user's account), instead of having a separate UserAccounts table to define that mapping. The question would be then whether (in other tables like Sales) I defined just the userId as a foreign key, or defined the userId+accountId pair -- that (storing the userId+accountId pair in more than one table) does seem to me to be an example of denormalization: isn't it?
Jan
3
comment Effect of denormalizing
@RibaldEddie Thank you for asking: in a word, "no". I edited the question to clarify that.