Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Posts
    6

    Storage of Collections of Boolean Fields

    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....

    Asset
    Id (PK)
    assetType (assume Accommodation or Attraction)
    assetName

    Feature
    id (PK)
    featureName

    AssetFeature
    assetId (PK)
    featureId (PK)
    FK 1 = assetId to Asset.id
    FK 2 = featureId to Feature.id

    Amenity
    Id (PK)
    amenityName

    AccommodationAmenity
    assetId (PK)
    assetType (persist appropriately for FK, assume 1 and that appropriate unique constraint is defined on assetId, assetType)
    amenityId (PK)
    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. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I like every single thing about your post apart from two things:
    1) Your doubt about this design
    2) Why do you have the assetType in AccomodationAmenity?

  3. #3
    Join Date
    May 2008
    Posts
    277
    One thing you might consider, especially if there isn't any difference between amenities and features other than an arbitrary classification, is to combine them into one table. Something like:

    Characteristic (id, name, group)
    AssetCharacteristic (asset_id, characteristic_id)

    The 'group' column would indicate "amenity", "feature", etc. This would also future-proof you against new groups being created.

  4. #4
    Join Date
    Apr 2010
    Posts
    6
    pootle...
    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).

    Futurity...

    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.

  5. #5
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by milkbag View Post
    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)

    AssetCharacteristic becomes a 3-way intersection table:
    AssetCharacteristic (asset_id, asset_type, characteristic_id, group)
    (asset_id, asset_type) references Asset
    (characteristic_id, group) references Characteristic
    (asset_type, group) references AssetGroup

  6. #6
    Join Date
    Apr 2010
    Posts
    6
    Thanks for the suggestion Future....
    Last edited by milkbag; 05-27-10 at 10:49. Reason: brain cramp

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •