Disk space requirements of additional fields vs additional records

[pharmacologist]

I'm trying to figure out if it's a bad idea to create an additional table with values rather than create a comma separated list in a field. I have a table that requires about 100 paired values (values are long integers) per record and am looking at around 500-750 of these records per year. Ideally, I would keep all these values in their own fields so that I can drill down and view/group them individually though this is not vital to my needs, more like a nice-to-have. I'm wondering what, if any, trade offs there are between creating a table with rows for these values versus creating an additional field in each record that has all the values separated, e.g. csv. I'm wondering how Access stores values whether they're in their own table or simply an additional field in another and how much more space that requires.

Thanks

[Micron]

There's a lot of considerations, none of which I suspect you need worry about at 700 records per year, especially if there are only 2 fields. You could probably have a million records before things start becoming problematic. Factors which contribute to the db limit of 2Gb are field data types (replication id is the largest), indexes, number of fields, number of tables and other objects, and to a lesser extent, the code project. So 2 tables of 5000 records each would result in a file size that is larger than one table with 10000 similar records - because each table adds its own overhead.

EDIT - I would avoid the comma separated values unless absolutely required and that's rare.

[moke123]

You'd likely be under a million records in that table after 10 years. You could split that table out to an additional backend file if you had concerns about size.

Edit: just for giggles I ran a loop of 1 million records with 4 numeric fields and the file size was only 33 mb's.