1 Reply Latest reply on Feb 25, 2009 5:30 PM by FentonJones

    need help with relationships in new database --subgroup



      need help with relationships in new database --subgroup


      I am designing a database with the first table-"person"  (containing various fields:name, birth date, age,  address, sex etc); this is linked to a second table, "clients"  and I plan to define, in a third table, "female clients" and a fourth table,"male clients" Some of the "persons serve in other roles, as "volunteers" (which will not be subdivided into male and female)and also as "researchers", so only the "clients will need separate tables that will subsequently have different activities 


      I linked the person ID primary key to the patient ID primary key and now wish to link the patient table to the male and female tables. While I can easily link the tables and establish a relationship between the tables, I am stumped about how to define the last tables by gender, when gender is stored as a field in the "persons" table.


      I assume that I need to make a calculation that if clients are female, they belong in the female client table and if not female (ie =male) they will by default be allocated into the male table.  


      Please help

        • 1. Re: need help with relationships in new database --subgroup

          In database design the most important thing you do is decide what the "entities" are of whatever it is you're trying to create a structure for. A "person" is an entity. A "project" is an entity. A connection of one entity to another entity is an entity of its own. For example, if person1 is working on project1, along with other people, then that particular connection is an entity in a project-people "join" table between People and Projects. This join table could also have a field, "role", where you could have attributes values like "volunteer" or "researcher". 


          But gender is an intrinsic attribute of a person. I can see no circumstance ever where a gender would require a table of its own.


          I can see a table of "genders", where you would have only 2 records, one with gender = "male", and one with gender = "female". This table's only use would be to filter genders from something like the People table. But the same thing can be accomplished using an unstored text field, _cMale_txt = "male" (etc. for female), in the People table itself, and a self-relationship using that field. That is what is usually done.


          I think that's why you're having trouble. It is not proper relational design, in my opinion. It may seem to solve some immediate need, but creates more problems than it solves.