I am modelling a Tourism related database that will support both a legacy printed guide and a web application. The database will contain all of a region’s tourism assets. Each asset is identified as being of a particular type (ex. Attraction, Accommodation, etc.). Each asset type has data that is particular to that asset type (ex. an Accommodation will have data that an Attraction will not).
One common general data pattern are collections of related Boolean fields. Some of these Boolean groups are common to all asset types, and some are particular to a single asset type. Two examples would be Amenities (air conditioning, cable/satellite tv, coffee maker, concierge, data port, etc.), Features (No Smoking, Wheelchair accessible, internet access, museum, government run, etc.). At this point you may be asking yourself what distinguishes a feature from an amenity, or even what the meaning of ‘feature’ is......in many cases I am at the mercy of governmental policy that dictates what must be shown in the printed guide (and how) so try to ignore the apparent illogic of the groupings (there is nothing I can do about them).
Another consideration to the design is that these Boolean groups frequently change; both elements within existing groups, and less frequently, new groups being introduced. This is currently a big problem for them because most if not all of these groupings are represented as bit columns within an unnormalized (denormalized implies intent to me ) database (actually even the groupings are not really defined within the db....you will see things like 20 bit columns named amenities01 to amenities20...sometimes several of these groups within one table).
So I am thinking something like (where Feature is common to all types, while Amenity is particular to an asset of type Accommodation....
assetType (assume Accommodation or Attraction)
FK 1 = assetId to Asset.id
FK 2 = featureId to Feature.id
assetType (persist appropriately for FK, assume 1 and that appropriate unique constraint is defined on assetId, assetType)
FK 1 = (assetId, assetType) to Asset table
FK 2 = (amenityId) to Amenity.id
So the presense of a row in one of the associative tables would indicate whether the 'bit is on'.
I am concerned that this model will result in some overcomplicated programming, but I am unaware of a suitable alternative. Is there a better way to do this ?
2) Why do you have the assetType in AccomodationAmenity?
This is to ensure that the AccommodationAmenity table only contains references to Assets of type Accommodation. In other words, so that I can define an appropriate foreign key from the AccommodationAmenity table to the Asset table (one that includes both the AssetId and the AssetType).
I have considered this as well, but it becomes problematic when you need to restrict what you are referring to as group characteristics to a particular asset type (as well as supporting those that are common to all asset types). I could not come up with an elegant design to support this....but then again, I started to question the underlying model so maybe I should reconsider.
I have considered this as well, but it becomes problematic when you need to restrict what you are referring to as group characteristics to a particular asset type (as well as supporting those that are common to all asset types).
Use another table to hold valid combinations of asset types and groups:
AssetGroup (asset_type, group)