basic table design question

[Andrew]

I was wondering what the best way to do this would be. I
want to create table(s) to store data that already exists
in tab-delimited files. Each file has ~1000 rows with 2
fields. 1 field is a short string and the other an int.
There are ~100 of these files and each file shares the
string field (i.e. the string field could be a key).
My question is: is it better to create 1 large table that
would have ~100 fields that are named based on the file
name (so basically a 100 x 1000 table), or should each
file's data be in it's own table then the tables could be
connected by their shared field (the string) or even just
an SQL join or something of the sort.

Thanks.

 

[Tim Ferguson]

is it better to create 1 large table that
would have ~100 fields that are named based on the file
name (so basically a 100 x 1000 table), or should each
file's data be in it's own table then the tables could be
connected by their shared field (the string) or even just
an SQL join or something of the sort.

You don't say much about what the files are for or about, but it would seem
that you need one table with three fields: Source, StringValue,
IntegerValue. This would be able to identify any particular value according
to where it came from. It's not possible to say what the PK would be
without further information.

HTH

Tim F

 

[Andrew]

Sorry, this is basically what each file would look like:

--------------------------------
| 10 letter string | int value |
--------------------------------

the string field represents ~1000 data points and the
int value represents the number of occurrences for each
point in the "test" represented by the file. So,
hypothetically if I had a file named for each calendar
year and
in each file the "string" was each day of the year
(1-jan, 2-jan, etc) and the value was how much rain fell
that day. What I would want to do would be to compare
across years, sometimes groups of years compared against
a single year or sometimes groups of years against groups
of years to look at trends. But again I have >100 of
these files each containing >1000 rows of 2 column data
with a single column common to all files.

thanks again.
Andrew