|
tanger has asked for the wisdom of the Perl Monks concerning the following question:
hey all!
I'm working with MySQL and may be confronted with building a PERL web application script that needs to handle a larcge/or rich database.
Basically I want to know whether to use rows or tables.
The web app is for members to create their own individual polls, and other members on the site can vote for a choice.
Now lets say this site gets big big--100,000 to 500,000 members (thats considered big for me).
Then lets say about 50,000 of those members decide to make a online poll each day then that will be a lot of database updating.
--So my question is, should I make individual tables for each member to keep track of their a poll (many tables since many members) or have the poll data inside a single table--therefore many polls would be many rows.
I also want people to keep track of their poll results/poll history--therefore I'm not sure by searching one single table with many poll data and rows is a good idea? What is considered a large amount of rows to search through?
Thank you all!
perleager |
Re: OT: MySQL - rows or tables
by dws (Chancellor) on May 29, 2005 at 05:50 UTC
|
So my question is, should I make individual tables for each member to keep track of their a poll (many tables since many members) or have the poll data inside a single table--therefore many polls would be many rows.
I'd use a single table, and would add a secondary, non-unique index on member id. (Without the secondary index, any search that included the member id would be linear through the entire table. With the index, you're searching a much smaller subset of the table.) Using one table gives you the benefit of (probably) keeping much of the index in the operating system's disk cache. That'd be a bit harder with lots of small tables.
| [reply] |
Re: OT: MySQL - rows or tables
by shlomif (Beadle) on May 29, 2005 at 07:33 UTC
|
Using separate tables with equivalent functionality is considered a bad idea, and also stands against one of the Relational Database Modules Normalization Rules. I suggest you put everything in one table, with different rows for different members.
Separate tables also make the queries more complex if not downright impossible. Plus, it may not be necessary that the database user of the connection has a table creation ability. Tables also often clutter the filesystem.
| [reply] |
Re: OT: MySQL - rows or tables
by astroboy (Chaplain) on May 29, 2005 at 19:06 UTC
|
You'll need more than a single table - at least two if you don't count the user table (I assume that it already exists)
+---------+
| user |
+---------+
| user_id |
+---------+
|
|
/|\
+-----------+
| poll |
+-----------+
| poll_id |
| user_id |
| header |
| question |
+-----------+
|
|
/|\
+-------------+
| option |
+-------------+
| option_id |
| poll_id |
| option |
| votes |
+-------------+
| [reply]
[d/l] |
Re: OT: MySQL - rows or tables
by Cap'n Steve (Friar) on May 29, 2005 at 06:02 UTC
|
dws hit the nail on the head. With proper index use, it shouldn't matter how big your table gets. | [reply] |
|
|
:) Thanks guys.
I just read a good article explaining more about indexes and what its used for!!!
I can't believe I did not know about this!!!!
For anyone who wants to read more about indexes and how it greatly improves MySQL queries, heres a link:
here!
thanks! perleager
| [reply] |
Re: OT: MySQL - rows or tables
by TedPride (Priest) on May 29, 2005 at 19:00 UTC
|
Hmm, more info on the rules regarding your polls might be good. Is there a max number of choices per poll? Can polls be edited before being released? Does poll history mean a breakdown of votes over a certain time period? How long does the poll history have to be available?
Given the features you want, you'll probably need two or three tables:
POLL VOTES TABLE
poll idn
choice idn
timestamp
voter name - or idn if all voters have to register
voter IP mask - if voters don't have to register
POLL CHOICES TABLE
poll idn
choice idn / rank in display
choice text
choice votes
POLL MAIN TABLE
user name and/or idn
poll idn
poll title
poll description
timestamp created / released
If the number of choices per poll is more rigid, you can merge MAIN and CHOICES and just assign each poll x number of choices to work with. | [reply] |
|
