Re: Best Design Approach
Posted by: Bill Karwin
Date: September 05, 2006 11:20AM

> 1) I am currently transcribing the data directly
> into MYSQL, however, there are people who will be
> assisting in other states. Thus, I wanted to
> create an Excel spreadsheet for them to use to
> import into MYSQL. If this is doable, how should
> the headings in Excel be labeled to lessen any
> import issues.

Probably you'll want to use the first table design I showed. It'll make it easier to import from Excel. You can use column headings in the Excel spreadsheet similar to the MySQL table's headings.

> 2) Based on my readings, an Excel to MYSQL
> converter is required. Is this correct?

The best choice is to save the Excel spreadsheet as a comma-separated text file, and then load that data into MySQL using the LOAD DATA INFILE statement. See http://dev.mysql.com/doc/refman/5.0/en/load-data.html.

You can also use the command-line tool mysqlimport, which does the same thing as LOAD DATA INFILE. See http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

> 3) As you probably know, Excel can easily add
> each male and female entries to together to give a
> total household count. Can this be imported into
> MYSQL?

Yes, but you should consider whether you want to store data redundantly like that. For instance, if you were to change either of the figures, the total would not update automatically as it does in Excel. In other words, MySQL (and most databases) do not store "formulas" as you can in Excel cells. The contents of a database column is only a value. So it won't update automatically if you change one of the other values.

> 4) Can the database be set up so that the total
> for for each sex breakdown would add automatically
> without entering the total amount for each line of
> info?

You can use what's called a VIEW to make some columns be computed as formulas. This is a bit different from the Excel notion of a formula in a cell, but you should be able to see how it can solve similar cases. See http://dev.mysql.com/doc/refman/5.0/en/create-view.html for information about views.

Regarding your question about the "duplicate key" error:
Primary keys must have a unique value on every row. You apparently loaded data without specifying a distinct value for the primary key. Some people declare an integer primary key as "AUTO_INCREMENT" but I omitted this from the example CREATE TABLE I gave above. You should read about it in the manual, to make sure you know what you're doing. See http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Regards,
Bill K.

Options: ReplyQuote


Subject
Written By
Posted
September 04, 2006 02:11PM
September 04, 2006 11:23PM
September 05, 2006 04:00AM
Re: Best Design Approach
September 05, 2006 11:20AM
September 07, 2006 08:56AM
September 07, 2006 09:23AM
September 05, 2006 04:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.