Database design - too many columns in a table?

Hi,
I would like to ask a question regarding correct database design
I am just a network admin and I am about to hack a simple database for our accountants
(we are a small company, so by "network admin" means "the one who does everything with computers :))

I have a table specification from our accountants - they want to store about 300 or so different number values for each customer and year...
these are just some financial data - each number represents a value of different financial indicator - they (at least I was told so) don't depend on each other

so the structure might look like this:

create table fin_data (
id_customer int not null,
year int not null,
data_value1 int,
data_value2 int,
data_value3 int,

....
data_value300 int)

now these value fields doesn't have any relations between each other, so basicly there might not be a need to separate them into different tables - on the other hand I really hate the idea, that I have a table with 300+ columns, so I was thinking about some kind of structure that would break down each row of this huge table into separate fields, so the table would only have 4 columns, something like this:

table field:
- id_customer
- year
- id_field_type (would describe which of the 300 indicators this one is
- field_value

However this structure would probably make writing a query a real pain - at least I can't think of an elegant solution on how to
retrieve data, so that original 300+ values per row appear e.g. when they will want to query such database in excel...

So, please, someone experienced - should I go with the 300+columns mega table, or try to break it down, in which case, what would be the easiest way to get the data into rows?

I hope my description is clear, if not, please do ask I will try to clarify :).

Thank you very much for any advice in advance.

The proper design to use is the second method.



The second method does not care exactly how many different values there are and it does not take up any storage space for any unused values. The first method would either require you to create the maximum number of columns or you would need to modify the table any time you need to add more columns.

The column with the "year" should actually be a DATETIME data type, so that you can query data for any previous time period.

If we assume you are talking about using mysql, the "basic" query to get all the data for any customer for the current "year" would simply be -



(assuming that the id_field_type is the order you want the rows to be returned as.)

Be honest with yourself. Is this something that will be used often and grow over time or is it something that will only be used occasionally and never be added to. If the first - your second design is better. harder to query against - but much easier to scale over time.

If the second - the you can go with your first design and pray it never becomes more then it is originally intended. Lower normalization makes things easier to query but can become a pain as things grow.

Also depending upon how you want do things - you can make a views of the most commonly requested info that the users can query on with their excel. This will allow you to give them all the info they need in one place to make their queries easier and help up the speed of those queries. But still have the base data for anything more advanced or comprehensive.