Database relation design - Relating two tables twice in different tables

I have the following tables:

Post
Id int

User
Id int

Then I have the table

Favo开发者_如何学Crite
PostId int
UserId int

and the table

Vote
PostId int
UserId int
IsUpVote bit
IsDownVote bit
LastActivity datetime2

the problem is that if I merged both Favorite and Vote into a single table, then I'd have something like

UserPost
PostId int
UserId int
IsFavorited bit
IsUpVoted bit
IsDownVoted bit
LastActivity datetime2

IsDownVote couldn't be computed anymore (since now, I can't use a "doesn't exist: didn't vote; didn't vote up: voted down" pattern anymore) and LastActivity will only reflect the last time the vote has changed (either up, down, or removed). So I'd maybe have to change that field's name or it's functionality. or even both..

So the question is basically, how wrong is having two tables relating Tables A and B (Post,User) in this case, which are indexed by the same primary key (PostId,UserId) in this case, but which are intended for different uses?

Favourites and Votes seem to be two different things, so IMHO you will be better off keeping them as separate tables. As you mentioned, you would lose functionality if you merged them, and I don't see any clear benefit to merge them. Stick with what you've got unless you can provide an awesome justification for the merge.

Nothing wrong at all.

I am not saying that the DDL provided shows correctly Normalised tables, but they are somewhat Normalised. As you have identified yourself, the two tables have different purposes, they have different meaning, so technically (theoretically, academically, and in practice [code] ), they are correct.

• "related to the same parents" is not a criterion (there are many instances where there are many tables related to the same parents, and which are correct)
• therefore such tables will "have the same PKs and FKs", so that is not a criterion either.

Only someone with no real concept of Normalisation, and no concept of the causes of negative performance, will suggest that "just because they have the same parents (and therefore the same pair of keys/indices)", they should be merged.

Vote and Favourite are two different Things, Entities, records of Action taken. Two tables is correct.

Distinction: The real reason IsDownVoted cannot be compared anymore is that it does not apply to Favourite. You have used an Indicator (bit) to identify that (although badly named); which is really a substitute for a Null column. Nulls are not good for performance, and it is a Good Thing that you have Indicators to identify the absence of data, and therefore avoided Nulls, but that is separate to breaking a Normalised design by mereging them.

The merged table will perform slower on all accesses. When you SELECT Votes from it, you have to exclude Favourites, and vice versa, but it will be doing I/O for both, because they are located together (PostId, UserId). SO the server is forever reading twice as many rows, using twice as much cache; etc. Then you will "add speed" by adding an index for (PostId, UserId, IsFavourited), making it even slower for Inserts and Deletes (while "speeding up" Selects). Messes get compounded, guaranteed; best to not have any mess in the first place.

When the database grows, you can independently add columns to either one of Vote and Favourite, without affecting the other. In a merged table, it will introduce complications.

You accept Answers too quickly.

While I won't say what you should do table wise if you use int instead of bit and use values like 0 1 and -1 to do calculations / comparisons, this way you could compute the values you want in a relatively simple way.

Talking relational databases you should almost always aim for 3'rd normal form regarding your tables - Try looking at http://en.wikipedia.org/wiki/Database_normalization

Cheers!