Why should i use 1 table instead of two?

I have two tables called (Up|Down)Vote, you can do BOTH upvote and downvote thus it isnt one table with a bool. People suggested i should use one table called votes and have a field called type.

Why should i do this? I think writing the unique table name开发者_JS百科 is easier then writing Vote then the extra where statement (type & upOrDownTypeBit) <>0.

NOTE: The (Up|Down)Vote table is PK id, FK userId

For data modeling, it is usually important to know what things mean.

Are an up vote AND a down vote on the same thing by the same person are the same as no vote at all by a person on a thing?

In any case, it's certainly possible (with appropriate indexing for performance) to have it in a single table for ease of management. On top of that base table you could add UpVote and DownVote views. Or you could go the other way and have two base tables with a single Votes view for convenience.

Typically, having two tables now means two sets of referential integrity, two semi-identical table schemas to be maintained, and a not-quite unified view of the data. It also means you might have to have two archive and/or partitioning plans for handling history, etc.

That doesn't mean it's wrong, but if entities are very similar, modeling them in a single table often makes a lot of sense.

Sometimes entities may appear similar, but in fact, they really aren't and they should be be modeled in separate tables. Quotes, Purchase Orders and Invoices come to mind here. A lot of people like to shoehorn two or more of these things into the same table, and that's really a bad idea.

Why not just make it an INT? I highly suspect that a BIT datum takes just as much space as an INT due to padding and such. With an INT, you could have:

   -1: downvote
    0: neutral vote (might not be used)
    1: upvote
 NULL: no vote

And your votes table might be:

create table votes (
    post INT,
    user INT,
    vote INT
);

The votes table should have foreign key constraints for post and user as well as an index on (post, user), but I don't know the syntax for that in MySQL.

One useful thing you could do by merging this into a single table is quickly finding the overall net vote by assigning +1 in a delta column for the table for an upvote and -1 in the delta column for a downvote.

Then you could go

SELECT SUM(delta) FROM votes WHERE id = ?

DRY. A disadvantage of having two tables is that any code that deals with votes will probably have to be repeated. That might include duplicating any constraints and other logic in the database. There is a relational database design principle that asserts that you should avoid repeating the same data in multiple tables: The Principle of Orthogonal Design.

Can a person have both an UPvote and a DOWNvote? If not, then two tables does not make sense. Also, you have to write two queries to remove a vote; one for each table. Is it still worth it?

If performance is a concern at all, then it would be faster to list from a single table rather than a join or a union on two tables.

Perhaps I'm misunderstanding what you're doing, but do you have some other foreign key regarding the object that is being voted upon? Or are users being voted on.

Either way, it would be easier and more performant to to aggregations on a single table, for example if you wanted to Sum() the upvotes or downvotes. You should probably do 1 and -1 in the vote field for that.

Your approach will provide some ease of use when reading the data back. A single table would provide ease of use when writing the data. With two tables you have to check the value before you decide which table to write to. With a single table, you write to the table without even knowing or caring what the contents of the variables are. I think this is a more normal way of doing things, especially when extended beyond this single example.

In order to allow both Up and Down votes, you could have a boolean column for each.

Do whatever makes the most sense in the context of your application. Keep in mind which systems will be consuming the data, and make sure what you implement makes sense for those systems. Also consider the cost of changing the tables if one day you discover you need to (this will tend to increase if there are multiple applications using the data).

But the bottom line is, it's your data, represent it in the way that makes things the easiest for you.

Actually, it depends on your data volume and what you gonna do with your data. If you don't have special reasons to use two tables, use one table. As one table can provide all functions you have with two tables, and it's simpler and easier under certain scenarios: Changing a up vote to down vote; sum all votes, etc.