Creating MySQL tables

tables or flat files

[hyperbole]

Is this the correct forum to ask a database question in?

I am creating a voting database. The customer wants the users to be able to vote for an item in a list only once and then the item will be removed from the list for that user. I thought it would be best to keep a list of the names of the items the user has voted for.

Should I keep the list of voted for items for each user as one large table with (user-id, item-id) pairs or should I create a table for each user in which I store the list of item-ids they have voted for or should I create a flat file for each user in which I store a list of item-ids they have voted for? What are the advantages and disadvantages of each method?

[andrew_m]

(user-id,item-id) gets my vote -- if you create a table per user it's a nightmare in debugging/maintenance. Plus, even if that's not a problem for you for some reason -- there are performance issues as well.

[SeanW]

As a general guideline in relational databases, if you're creating tables on the fly (temp tables don't count), you're doing it wrong ;)

"Customer" is an entity, and is usually a table on its own. "Fred Smith" is an instance of a customer entity, and should be a row in the customer table.

I'd also think about indexing the customer_id field for quicker lookups if you're going to reference it that way.

Sean

[hyperbole]

Andrew: If I put (user-id, item-id) pairs in a table, won't the speed of access degrade as the size of the table grows?

Sean: I have a "customer" table with "Fred Smith" as a row in the table. This table is indexed on user-id. How do I store the fact that Fred has voted for item-1, item-3, and item-5 and then he can com back tomorrow and vote for item-2, but not the items already voted for?

[andrew_m]

> Andrew: If I put (user-id, item-id) pairs in a table, won't
> the speed of access degrade as the size of the table grows?

Not really, presuming that you have an index on (user_id,item_id).

If you put each user in its own table -- the system still has to find somehow which table to use, and it will most probably use some implicit indexing or search in directories to do so. Don't assume that since you know table name, there is no cost in accessing it.

Whereas explicit index you create on that single table is optimised by the sql engine, cached in memory and so on.

Not to mention that if you use mysql/myisam tables -- it's 3 files per table -- another performance hit and you're likely to hit max. open files limit as well.

[SeanW]

Hi,

You can figure out which have been voted for with

select voted_for from user, vote where user.user_id=vote.user_id and vote.poll=XXX

I'm sure it's easier with subselects, but mysql doesn't support 'em...

Sean

[hyperbole]

Thanks to both of you.

If I understand you correctly, you're both advocating one large table of user-id, item-id pairs as being the fastest and requiring the least system resources.

I assume that using the database to store this is also faster than using flat files.