Theoretically it is simple to have an attributes table that has two fields: attribute and value. You can then provide the attribute field with a label, ie, gender, and the value field with male or female. The next record might have a different attribute, such as "ethnicity."
This sort of construction is useful in circumstances where you might expect users to collect ad-hoc data. An example might be a contacts database where you want a knowledgeable sales team to be able to extend the data set in unusual ways, such as "football team: Dockers" or "children: esther, will".
There is an absolute minimum in overhead in creating this system when you only want to put data in. But you need to manage the way in which you report on these. A report on gender has to find all "attribute"="gender" and then summarise by "value". That's kind of extra work may be fine for information that you only want to report on occasionally. However, If you expect the majority of records to carry a value for any of these attributes then you should simply make a field for them. Whether you store them in the same table or in a related table with a one to one relationship is up to you.
Thanks for this advice. I don't need the flexibility of adding ad-hoc data in unusual ways, and, yes, most of the records will have values for some of the attributes ("gender", "ethnicity", etc.), but not for others ("likes football", "trustee", etc.). So while I'm keen to treat all attributes in the same way (in the past I've been tripped up by not doing so, when it comes to reporting), I think you're suggesting that I use separate fields for some (i.e. the ones that are held by the majority), but a separate attributes table for the rest. Or have I misunderstood?
1 of 1 people found this helpful
That's mostly right.
Building this sort of thing for collecting and displaying attributes against a record is easily done. The real trick is to factor in the reporting component.
If you need to report on the unusual attributes at the same time as the common attributes then it may be better to have one system. Otherwise you have to generate two reports using different methods and that increases workload/complexity.
Thanks again Malcolm. I'm going to sit on it for a bit - part of me is thinking that all attributes belong in the same bucket (and I guess in terms of mormalisation rules that's definitely the case), but when FM makes it so easy to do it otherwise (i.e. with a simple "gender" field, and a couple of Boolean calcs - "IsMale" and "IsFemale" - for counting and reporting), it's also tempting to go down that route.