I agree with what has been said so far regarding normalization, getting the underlying structure right, and using views to access the data. I would add a couple of points:
Database refactoring is not necessarily as hard as has been suggested. In fact, a good design, separating data storage (tables) and data access (views) will not only minimize the need for future changes, but make such changes easy to do when required. For instance you can change the tables while retaining the same views, or vice versa. Writing queries to migrate data from old to new schemas without interrupting operations (on databases suitably designed to enable this) is every-day work for DBA's. Normalizing your data is a good idea, but the question really is what is normalized in this case? Treating the set of fields and their names as data (as in your design B) may be the best way. However, were this mode of reasoning taken too far, you might end up with a database having no structure at all, besides that implied by the data stored in it (which is open to interpretation). The whole point of SQL is that it provides tools to help specify the structure of your data. Making schema changes, such as adding or removing attributes, is not necessarily difficult. This is what DML is for. However one thing SQL does not handle well is temporal schemas, i.e. ones in effect during different periods of time. The main reason why you might need to use design B instead of A in this case is if you need to be aware of which fields were valid at past times or will be valid in the future; for instance, if you remove a field but you do not wish to lose the information previously reported while that field existed, or more subtly, if after adding a new field, you need to know that past reports explicitly did not include that field. There are also other reasons why the field names seem more like data than schema in this case and I would agree that, based on the information provided so far, design B is almost certainly better. I am just making the point that the question is a little more subtle than the people saying 'you must normalize' have implied, and you are right to try and better understand the reasoning behind that opinion. -- Mario Becroft <m...@becroft.co.nz> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
