I need to record people's ethnicity. It's a 2-part piece of
information, made up of main group (e.g. A - Asian, i.e. alpha code
and name) and sub group (e.g. 2 - Indian, i.e. numeric code and
name). There are 5 main groups, with 4-5 sub-groups within each, so
around 25 possible categories altogether (A2, A3, B1, etc.). I will
need to do a lot of reporting and analysis by ethnicity (main group
and sub-group) on the data.

Options I've considered:

1. Just hold the values as a custom value list, and hold the ethnicity
(main and sub) as a text field on each person's record.
2. Hold all possible combinations in a single "ethnicities" table, and
hold the key on the person's record.
3. Have 2 additional tables, one for main groups, and one for sub-
groups. (This seems to be the most "correct", normalised way of doing
it - but is it overkill?).

Any recommendations/comments would be very welcome.

Carla.

In article <1188826023.261178.85920 [at] r29g2000hsg.googlegroups.com>,
Carla <carla_sloan [at] yahoo.co.uk> wrote:

> I need to record people's ethnicity. It's a 2-part piece of
> information, made up of main group (e.g. A - Asian, i.e. alpha code
> and name) and sub group (e.g. 2 - Indian, i.e. numeric code and
> name). There are 5 main groups, with 4-5 sub-groups within each, so
> around 25 possible categories altogether (A2, A3, B1, etc.). I will
> need to do a lot of reporting and analysis by ethnicity (main group
> and sub-group) on the data.
>
> Options I've considered:
>
> 1. Just hold the values as a custom value list, and hold the ethnicity
> (main and sub) as a text field on each person's record.
> 2. Hold all possible combinations in a single "ethnicities" table, and
> hold the key on the person's record.
> 3. Have 2 additional tables, one for main groups, and one for sub-
> groups. (This seems to be the most "correct", normalised way of doing
> it - but is it overkill?).
>
> Any recommendations/comments would be very welcome.
>
> Carla.

If there's no realistic chance of the group/subgroup combinations
exploding, the best approach is #2. The formal answer depends on how
many total subgroups there are. If the combinations can be completely
represented in a grid with few invalid combinations, then 2 additional
tables would be the answer, but if there's not significant overlap among
the lists of subgroups associated with each group a single lookup table
still works. The point of the second table is to eliminate redundancy
and if there isn't any...

Carla wrote:
> I need to record people's ethnicity. It's a 2-part piece of
> information, made up of main group (e.g. A - Asian, i.e. alpha code
> and name) and sub group (e.g. 2 - Indian, i.e. numeric code and
> name). There are 5 main groups, with 4-5 sub-groups within each, so
> around 25 possible categories altogether (A2, A3, B1, etc.). I will
> need to do a lot of reporting and analysis by ethnicity (main group
> and sub-group) on the data.
>
> Options I've considered:
>
> 1. Just hold the values as a custom value list, and hold the ethnicity
> (main and sub) as a text field on each person's record.
> 2. Hold all possible combinations in a single "ethnicities" table, and
> hold the key on the person's record.
> 3. Have 2 additional tables, one for main groups, and one for sub-
> groups. (This seems to be the most "correct", normalised way of doing
> it - but is it overkill?).
>
> Any recommendations/comments would be very welcome.
>
> Carla.
>


If you use separate tables, you can use unique serial ID's for both
Group and sub-group. If the data is coded with ID's then searches, count
calculations, stats etc. can be structured to uses these, and will be
faster, and more flexible...


regards

Chris

On Sep 3, 2:27 pm, Carla <carla_sl... [at] yahoo.co.uk> wrote:
> I need to record people's ethnicity. It's a 2-part piece of
> information, made up of main group (e.g. A - Asian, i.e. alpha code
> and name) and sub group (e.g. 2 - Indian, i.e. numeric code and
> name). There are 5 main groups, with 4-5 sub-groups within each, so
> around 25 possible categories altogether (A2, A3, B1, etc.). I will
> need to do a lot of reporting and analysis by ethnicity (main group
> and sub-group) on the data.
>
> Options I've considered:
>
> 1. Just hold the values as a custom value list, and hold the ethnicity
> (main and sub) as a text field on each person's record.
> 2. Hold all possible combinations in a single "ethnicities" table, and
> hold the key on the person's record.
> 3. Have 2 additional tables, one for main groups, and one for sub-
> groups. (This seems to be the most "correct", normalised way of doing
> it - but is it overkill?).
>
> Any recommendations/comments would be very welcome.
>
> Carla.

Thanks guys, for these helpful replies.

"Carla" <carla_sloan [at] yahoo.co.uk> schreef in bericht
news:1188826023.261178.85920 [at] r29g2000hsg.googlegroups.com...
>I need to record people's ethnicity. It's a 2-part piece of
> information, made up of main group (e.g. A - Asian, i.e. alpha code
> and name) and sub group (e.g. 2 - Indian, i.e. numeric code and
> name). There are 5 main groups, with 4-5 sub-groups within each, so
> around 25 possible categories altogether (A2, A3, B1, etc.). I will
> need to do a lot of reporting and analysis by ethnicity (main group
> and sub-group) on the data.
>
> Options I've considered:
>
> 1. Just hold the values as a custom value list, and hold the ethnicity
> (main and sub) as a text field on each person's record.
> 2. Hold all possible combinations in a single "ethnicities" table, and
> hold the key on the person's record.
> 3. Have 2 additional tables, one for main groups, and one for sub-
> groups. (This seems to be the most "correct", normalised way of doing
> it - but is it overkill?).
>
> Any recommendations/comments would be very welcome.
>
> Carla.
>

Just a bit of theory. When thinking about separate tables or not you have to
think about what exactly you are going to store in them and how the data are
going to relate.
You have Objects, these always reside in a separate tabel. Objects might be
customers (one record for each customer), or machines (one record for each).
Then you have Attributes, who might or might not need separate tables. For
instance a customer (Object) might have an address (attribute of customer).
As long you are very sure a customer will have only one address you can put
an addres-field in the same table with the customers name (it is really an
object now). But as soon as a customer has more addresses it becomes an
attribute and has to reside in a separate table. Then there are unique and
shared attributes (like machine parts), many machines will share many
components, which require a many to many relation with a join table. With
etnicities and two choices (main and sub) I would create one extra table
with one field (and an ID field) holding all possible ethnicities. One item
per record only. Then create a value list and a calculated value list. The
first one holds all possible values come from the table (no relation
necesarry), the second one holds all, but entry is validated in such a way
that double entrycan't occur.

Hope all this theory is clear

Keep well, Ursus