2500 columns...is mysql for me?

[lindm]

I have some little experience of mysql and php and am planning a project that could involve 2500 columns of a database. The project is nothing fancy but a financial report for companies. I am however not sure this is such a good idea or even possible?? Would much appreciate advice on the matter.

 

For you to get the idea, the table would basically be set up with the following columns:

 

ID

Password

Company

Setting1

...

Setting50

Turnover

...

ProfitLossforTheyear

Asset1

...

Asset100

TotalAssets

Equity

...

Debt1

...

Debt100

TotalDebt

Notedata1

...

Notedata1500

 

 

[Barand]

Yesterday I told you that MySQL is not a spreadsheet and you had a problem with your design. I gave you this link to read:

 

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

Two hours later you post this. So, clearly, any advice we give you is completely ignored.

 

Why should we waste our time?

[lindm]

Hello Barand,

 

Understand your point. For a non-native english speaking person and a basic skilled mysql user the article has somewhat complicated text:

 

I have read the article but can mainly see one "normalization" issue: the company data and the financial report data. The fact still remains that I will have approx 2500 columns of "spreadsheet data" each company. Is splitting the financial report in different tables (ie one for settings, one for income statement, one for balance sheet, one for notes etc) really necessary? Each column will mainly contain figures not exceeding a 6-figure amount. Each column of the financial statement is unique for the company and not like an "author-column" in the article example.

 

Where ever I read the main idea is that mysql databases over 30-60 columns is not designed well. I will never come down to this figure...

 

My basic question is if a mysql database with 2500 columns containing unique very little data will cause problems? What type of problems (the article mentions "data redundancies, data anomalies, and various inefficiencies", please help me understand)?

 

Thank you

[fenway]

All of your FieldName1...N need to be pulled out in separate tables.

[lindm]

Fenway,

 

Hope you don't mean one table per field??

 

Perhaps the following setup is better?

 

Table User : Contains user data, approx 5 columns

Table Settings : Contains user settings, approx 30 columns

Table Income : The income statemetnt, approx 100 columns

Table BalanceAsset: Balance sheet Assets, approx 100 columns

Table BalanceDebt: Balance sheet Debts, approx 100 columns

etc

etc

 

 

[fenway]

Fenway,

 

Hope you don't mean one table per field??

What do 1...N represent?  Are they the same for each "field type" (e.g. balance asset, balance debt, etc.)?

[lindm]

1...N mainly represent unique form elements such as text fields, text areas as well as some checkboxes. The user will enter numbers or text into the form elements press save to save each element to the database.

 

So each user will have a set amount of unique form elements/mysql columns.

[fenway]

Unique for each form or user?

[lindm]

Only one really large form which is the same for each user. The user enters data into the form which forms the database entry. All data (form field input) from the form is unique for every user.

[fenway]

I suppose if the fields are entirely unrelated to one another, then your earlier example of one table per "section" would be adequte, though far from ideal.

[lindm]

Much appreciate your input Fenway.

 

So I will probably have some table with 500 columns...are there any risks or problems with this large amount of columns?

 

Is there any way to make the setup more ideal?

 

Should I perhaps consider other database solutions?

 

 

 

[revraz]

Problems?  Wait till you start trying to do queries on them.

 

You should consider other design solutions, not database solutions.

[Barand]

What determines whether the entry of a number should be in Debt1 or Debt2 rtc?

 

What determines whether the entry of a number should be in Asset1 or  Asset2 etc?

[lindm]

Response to Barand,

 

There is a fixed input field in a html form for each mysql column. For instance the contents of input field with id/name Debt1 will be recorded to column Debt1 in the mysql database when the user presses Save.

[lindm]

Revraz,

 

Please specify problems. Will queries stop in the middle, will data be lost?

 

I basically want to do 2 queries:

1. SELECT * from table with echo $row[columname]

2. UPDATE table

 

 

[Barand]

I'd set it up as several tables like this

[pre]

User                   

=====                 

ID        --------+                  ----+                        ----+

Password          |    Income          |      BalanceAsset          |      BalanceDebt

Company          |    ==========      |      ==============        |      =============

Setting1          |    idIncome        |      idAsset                |      idDebt

Setting2          +---< userID          +----< userID                +----< userID

...                    dateRecd                assetDescrip                  debtDescrip

SettingN                amount                  amount                        amount

[/pre]

 

For example, instead of 100 columns in one record for income, have a table with a row for each income amount, added as you need to.