this evaluation can only be done with existing apps, which have run for a long time, so you have some data to analyse
> >Yes this is very true. In fact I think it should be considered a best practice to revisit and analyze your data on a regular basis, say once a year, because things change with time, and it can be difficult to extrapolate from a change in business practice to a change in field usage. >
I agree
>>
There is a fine line which will have to be tetermined on a field by field basis. >>
you could end up with a lot of new tables
>>
you need to determine at what point you use more space adding a new table with 2 fields instead of using 1 field
> >This is fairly straight forward. In the following assume 1000 == 1024. If you have a 40 character text field with 4000 variations in a 1 million row table, then current space is 40MB. 4000 variations require only 2 bytes as a pointer / key. So the alternative structure is:
The space saving is huge : 2.172MB is considerably less than 40MB. > OK, that's where i get lost. where do you get the 4000 variation from? what variations? are you talking about possible letter/number variations in a character field (that should be quite a bit more), or are you talking about empty space and filled space possibilities? or are you talking about 'current' variations (different entries in the table) then of course you should add at least 1 byte to allow for future possibilities. But i must agree here with the calculation, you would save plenty of space.
>>
more tables to be maintained, and more tables which could develope problems
> >With lines of code, I agree, the more there is, then the greater the chance of errors, I'm not so sure that this also applies to tables.
well, the errors are possible to be found and fixed. that's the work of a developer. but i've heard somewhere <bg> that people have table/index corruptions. i myself havn't had that problem yet. in 6 years, where i've worked with mrp systems (the old one has 428 Tables and 4.2 GB) and the one i am currently developing, i never had any table corruptions - well, that is not counting that one user, which went: "Oh sh..., that's not what i wanted" and switched the computer of right in the middle of processing. well - i got her straightent out! <s>
> >>
the more files you have in a folder, the more your app will slow down
> >Not necessarily, the files 'should' be considerably smaller, allowing more data to be cached, which may translate into a faster system. John Holubiak and I have been investigating this (on a casual basis) for quite a while - expect a white paper sometime in the decade.
i absolutley do not agree here. from my own experience with the obove mentioned older mrp system i can defenetly tell you that there comes a time (if the files keep adding up) where the system WILL slow down! I am talking here about 2000 - 3000 files in 1 single folder. that old system does not clean up temporary files, so at the beginning they kept piling up. and man - let me tell you, after deleting all of them, that app was happy like a camper and real fast. > >> >>of course if you've reached 2GB - how cares - you have no choice, and that's where the owner of this thread comes from. >> >>I guess my question here is: when do you start taking those considerations? When do you start spending time to plan, design, and implement a more complex database architecture just because it might happen? > >That would be the difference between a database developer and a VB programmer! hmm, i hope you didn't just call me a VB programmer. <s>
