Q: I have an application that tracks real estate properties for sale. Each property can have any number of amenities such as number of bedrooms, outdoor Jacuzzi, granite countertops, etc. Currently I track these with Boolean fields in the Property table. I have a container with a checkbox for each Boolean field where the user can specify which amenities a property has when he is adding or editing property information and when they want to query for properties that have certain amenities. Every time I add an amenity, I have to add another Boolean field to the table, another checkbox to the page where the user can specify amenities, and another case statement to the code that creates the query string. This is getting very cumbersome and I was wondering if there isn't a more generic way I might handle this scenario?
A: There are a couple of things I would do to simplify maintenance of your application. First, I would change your data structure. Although an argument can be made that having Boolean flags is faster for data retrieval than a normalized many-to-many table, with today's fast processing speeds and data connections, I don't believe the speed difference offsets the maintenance cost for such a scheme. And, as easy as it is to assign a Boolean field to a checkbox as a control source for simple data modification, I believe the cost in application maintenance is too high. Instead, I recommend you create a table (I call it the attribute table) that contains a record for each possible amenity available in a property, and another table (the many-to-many table) that links the property to its amenities.
There are many scenarios where this sort of design and data pattern would be appropriate. Other examples include a customer and his hobbies, a recipe and its ingredients, a houseplant and the nutritional inputs it needs, and a patient and the medications he takes.
To create one set of classes that can be reused, I have some rules about the structure of the two tables. I try to make my table names no more than 8 characters so that the primary key can always be named pk<table name> and remain no more than 10 characters. This allows me to have index tags with the same name as the field, and to use the field in temporary free tables. In your case, the real estate property table would be named Property, and its primary key would be pkProperty.
I always give the attribute table a cValue field in addition to the primary key field. In your case the table would be structured as in table 1.
To be consistent with my naming convention, I would name the many-to-many table something like Prop2Am and it would have the structure shown in table 2.