Best way to design table for (gender & province)

[calvinle]

Hi,

I need to reproduce a table as per below and I would like some feedback on how to design such table?

Province of Employment | Participating employees | Male | Female
BC
AB
SK
MB
ON
QC
NB
NS
NL
PE
Federal

I'm thinking 2 tables:
The first one with 12 columns, one for each province + PK id.
The second table with 4 columns (male, female, PK id, and province_id which is related to the first table).

or my second thought will be:
Only 1 table with 23 columns (11 province x 2 genders, and PK id).
So basicaly, BC_male, BC_female, etc..

Any issue doing 2nd option?

Thanks

 

[theDBguy]

Hi. What kind of information are you trying to store in your table? Are you trying to count the number of employees in each category?

 

[calvinle]

The number of male and female per province.
Province is already predetermined.

Thanks

 

[isladogs]

This is a spreadsheet layout. Why not do it in Excel?

If you want to do it in Access, then change the layout so the table is normalised.

Assuming you are recording names then have one table with fields
EmpID -autonumber PK
LastName
FirstName
Gender
Province

If you are just recording employee numbers then one table with fields
ProvinceID - autonumber PK
Province
TotalEmployees - number
Gender

All fields text unless otherwise stated

Either way, you would run a crosstab query to get a spreadsheet like layout

 

[isladogs]

Not quite right. Technically, you need three tables. One to define provinces:
tblProvince
ProvinceID (autonumber PK)
Province

The OP stated that Province was 'predetermined'. I assumed that meant either that a Province table already existed or that the province code was all that was required.

A second to define Gender values. You can limit this to Male and Female but the Woke are going to lobby for it to be 57 ( or 27,000) values.
tblGender
GenderID (autonumber PK)
Gender

Hmm...oh really?????
I have NEVER created a tblGender.
Normally I use a value list in form comboboxes to include M, F and other transgender/intersex values where required. If it ever gets to a large number being needed then a table would be a good idea but at the moment I think its superfluous

 

[Mark_]

Hmm...oh really?????
I have NEVER created a tblGender.
Normally I use a value list in form comboboxes to include M, F and other transgender/intersex values where required. If it ever gets to a large number being needed then a table would be a good idea but at the moment I think its superfluous

How gender biased of you! I figured you'd not post something like that lest the police find out and incarcerate you for posts hurting others feelings!

@OP, something that may be relevant to your structure, are there cases where you know someone is employed but don't have gender information on them?

 

[isladogs]

If ProvinceID is the autonumber for a Province then you can have only a single gender value per province.

Definitely not true.
Its name may have been unintentionally misleading but the autonumber ID field will not prevent you having two (or more!!) records for each province for male/female ...

 

[isladogs]

@Pat
Not sure this exchange is helping the OP but one of us may be missing the point here....
Screenshot shows table design and example data.

In view of the previous comments I deliberately included some non binary gender values. If it was a real database I would try to shorten to one or two letters e.g. N, Q, FB

I particularly liked the term QUILTBAG
For anyone else who, like me, didn't know any of those, see https://www.mic.com/articles/52001/9-gender-and-sexuality-acronyms-you-should-learn

 

[Mark_]

Colin,

But you forgot all about those Yiffing Furries! How could you?!?! So exclusionary....

 

[isladogs]

Mark
I do apologise for excluding you from my example
If we include sexual preferences as well, the list would be endless.

Anyway, I've learned something new that I hope I can quickly forget ...

 

[Mark_]

Colin,

Not my thing, but there are soo many people who claim to identify in strange ways its hard to keep up with. Have to make sure that this individual doesn't feel excluded, now don't we....

 

[isladogs]

Mark
That's even worse...
I'm going to try NOT to click on any more links you provide on this topic.

Pat
I've no disagreement with that point about indexing. Obviously you don't want 'duplicate records' in any table

Calvinie
Are you still there or has this veered into realms you don't want to get involved with?