in reply to Reading data from mySQL into hashes and manipulation of hashes

G'day all

This is one of the many reasons I visit this site and community, they are damn good and pleased to assist. Thanks once again!

The current set up of the database is as follows:

1 table of a mySQL database. (each line is a column) 
item_no (the number of the poll in the database)
date_begun (the date the vote was added)
date_ended (the date the poll stopped)
begun_by (the username of the person who created the poll)
description (the text above the vote)
option_1_t -> option_6_t (6 columns containing the text of the vote op
+tion)
option_1_n -> option_6_n (6 columns containing the numbers attached to
+ vote options)

[download]
This is the current layout of the database...

As you've probably guesses, I'm fairly new to DBI and perl in general, so any pointers and documentation recommendations gratefully accepted!

How could the database be better designed ?

Thanks again
lagrenouille

Replies are listed 'Best First'.
Re: Database Design
by tommyw (Hermit) on Aug 30, 2002 at 15:07 UTC

    Neither a perl problem, nor a DBI problem

    Remove the options columns, and create a secondary table. This will have 4 columns, and (upto) 6 rows for each entry in the main table.

    --
    Tommy
    Too stupid to live.
    Too stubborn to die.

[reply]
      G'day all

      Would you care to elaborate ?

      ie. What is a secondary table ?
      How is one created ?
      How do you link it to the main database ?
      Or is it not linked and merely referred to ?

      Cheers
      lagrenouille
[reply]
        What is being suggested by more than one person is to simplify your database by creating another table to your data is more easily obtained. The suggestion given above is a good one and might look like this:

        ---------------------------------------------------------
| Table name:  polls                                    |
---------------------------------------------------------
| poll_no | date_begun | date_ended | begun_by | descrip|
---------------------------------------------------------

-------------------------------------------------
| Table name: votes                             |
-------------------------------------------------
| poll_no | option_no | option_text | num_votes |
-------------------------------------------------

        [download]

        Now think about how you'd approach your problem given that you have a more normalized database to work with.

        ~CubicSpline
        "No one tosses a Dwarf!"

[reply]
[d/l]

In Section Seekers of Perl Wisdom