Table design question: (var)char vs. datetime/interval

Dear Informixers, I inherited a legacy application that stores most data in one large flat table. There is a unique record ID that serves as primary key for this table. Now there is a requirement for storage and processing of additional information for each new record, most of it timestamps, but also some other data. These new requirements are dynamic in nature, meaning that it is very likely that management will come up with additional ideas in the near future ;-) In other applications, I have very good experience with storing information in attribute/value pairs, so when a new requirement arises, I can just define an additional attribute and be all set. For the "value" column, I always used the "char" datatype. This works fine even when numeric values and computations are necessary, IDS does the implicit conversions for me. Now is the first time I want to use this approach for datetime and/or interval data. Since we are on IDS 10 which supports casting of data types, could I just write the data in the necessary format (e.g. YYYY-MM-DD HH:MM:SS) into a "char" or "varchar" column and use casts to datetime/interval types when doing datetime/interval arithmetic either in SQL or ESQL/C? Or are there any good reasons not to do this, and use explicitly declared datetime and interval columns instead? Regards, Richard

Richard Spitz wrote: > Dear Informixers, > > I inherited a legacy application that stores most data in one large flat > table. There is a unique record ID that serves as primary key for this > table. > > Now there is a requirement for storage and processing of additional > information for each new record, most of it timestamps, but also some other > data. These new requirements are dynamic in nature, meaning that it > is very likely that management will come up with additional ideas in > the near future ;-) > > In other applications, I have very good experience with storing information > in attribute/value pairs, so when a new requirement arises, I can just > define an additional attribute and be all set. For the "value" column, > I always used the "char" datatype. This works fine even when numeric > values and computations are necessary, IDS does the implicit conversions > for me. > > Now is the first time I want to use this approach for datetime and/or > interval data. Since we are on IDS 10 which supports casting of data types, > could I just write the data in the necessary format (e.g. YYYY-MM-DD HH:MM:SS) > into a "char" or "varchar" column and use casts to datetime/interval types > when doing datetime/interval arithmetic either in SQL or ESQL/C? Or are > there any good reasons not to do this, and use explicitly declared datetime > and interval columns instead? > > Regards, Richard And the good reason for *not* using datetime / interval columns? Myself, I would use the "data type" required; what if someone puts in "Oh work this out then" in your varchar and then you try and do datetime / interval arithmetic against that?

On Jun 2, 8:48 am, Richard Spitz <Richard.Sp...@med.uni-muenchen.de> wrote: > Dear Informixers, > > I inherited a legacy application that stores most data in one large flat > table. There is a unique record ID that serves as primary key for this > table. > > Now there is a requirement for storage and processing of additional > information for each new record, most of it timestamps, but also some other > data. These new requirements are dynamic in nature, meaning that it > is very likely that management will come up with additional ideas in > the near future ;-) > > In other applications, I have very good experience with storing information > in attribute/value pairs, so when a new requirement arises, I can just > define an additional attribute and be all set. For the "value" column, > I always used the "char" datatype. This works fine even when numeric > values and computations are necessary, IDS does the implicit conversions > for me. > > Now is the first time I want to use this approach for datetime and/or > interval data. Since we are on IDS 10 which supports casting of data types, > could I just write the data in the necessary format (e.g. YYYY-MM-DD HH:MM:SS) > into a "char" or "varchar" column and use casts to datetime/interval types > when doing datetime/interval arithmetic either in SQL or ESQL/C? Or are > there any good reasons not to do this, and use explicitly declared datetime > and interval columns instead? Beware of 'Entity, Attribute, Value' (EAV) models of data - see: http://en.wikipedia.org/wiki/Entity-Attribute-Value_model Also beware of 'One True Lookup Table' (OTLT) designs. You can find multiple debates in comp.databases.theory discussing the various (de)merits of these techniques - the acronyms are pretty good search terms. -=JL=-

TBP <thebigpotato@nothere.co.uk> schrieb: >And the good reason for *not* using datetime / interval columns? Flexibility. And yes, I know about the pros and cons of the EAV approach. >Myself, I would use the "data type" required; what if someone puts in >"Oh work this out then" in your varchar and then you try and >do datetime / interval arithmetic against that? No user has direct access to the database. And in case an invalid entry made its way into the varchar column, this would have to be caught by proper error handling which is necessary anyway. Regards, Richard

Jonathan Leffler <jonathan.leffler@gmail.com> schrieb: >Beware of 'Entity, Attribute, Value' (EAV) models of data - see: > >http://en.wikipedia.org/wiki/Entity-Attribute-Value_model Thanks for the link. Indeed I am working in a clinical environment, that's why this approach is so appealing to me. >Also beware of 'One True Lookup Table' (OTLT) designs. Luckily, this is not relevant in this context. >You can find multiple debates in comp.databases.theory discussing the >various (de)merits of these techniques - the acronyms are pretty good >search terms. None of the downsides of the EAV model have really materialized in my environment, which is mostly due to the fact that our databases are rather tiny compared to what the real pros in this newsgroup are used to. I'm not planning to play in the major leagues ;-) The requirements for the current project are far from being finalized, so I might end up creating a table with datetime columns. I just want to evaluate the available options. According to a reply I received by mail, using a (var)char column with the appropriate casts should be possible. I'll build a small test case and report the results. Regards, Richard