I have recently been introduced to the new Access 2007 feature which is multivalued fields. My initial impression is that it is a bad idea to make use of multiple values in a single field. Traditionally if you wanted to allow for a record to have several values for a field you would create another two tables and link them with foreign keys. This allows for easy querying and ensures that duplicate values reference the same item. Keeping lists in a cell seems like a violation of the purpose of databases.
Are there good uses for these fields which don't make me feel dirty?
See:
Multivalued datatypes considered harmful: How dangerous can a data type be?
I had a long talk with Suraj Poozhiyil, the Access Program Manager... both Suraj and I agree wholeheartedly that developers do not need to use multi-valued fields. People who understand databases already have a good way of implementing many to many relationships and will gain no benefit from multi-valued fields.
So, my clear and certain advice to developers is not to use multi-valued fields. They have nothing to offer us except potential pain.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With