Can't figure out table structure!!!

[dp1981]

I have no idea WHY I can't seem to figure out how to construct relational tables, but I just can't get it! I've built 2 other databases, but always had to come here for help on making the tables. So here I am again, only this is a bit more complicated than those I've posted about before. So I apologize for the length, but I'll try and post EACH field now so whoever may answer can possibly understand my frustration and confusion. The fields I need to have are as follows (separated by categories to shorten length here):

SoldierLastName (txt)
FirstName (txt)
MiddleName (txt)
Suffix (txt)
SSN (#)
DOB (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
Ht (#)
Wt (#)
DOR (date)
PEBD (date)
APFT (date)
Blood (txt)
Meds (txt)
Allergy (txt)
PHA (date)
Mobd (yes/no)
93 (date)
8286 (date)
Tags (yes/no)
Profile (#)
FCP (txt)

MomLastName (txt)
FirstName (txt)
MiddleName (txt)
MaidenName (txt)
Deceased (date)
Street (txt)
City (txt)
State (txt)
Zip (#)

DadLastName (txt)
FirstName (txt)
MiddleName (txt)
Suffix (txt)
Deceased (date)
Street (txt)
City (txt)
State (txt)
Zip (#)

SpouseLastName (txt)
FirstName (txt)
MiddleName (txt)
MaidenName (txt)
Suffix (txt)
DateMarried (date)
DateDivorced (date)
DOB (date)
Street (txt)
City (txt)
State (txt)
Zip (#)

ChildLastName (txt)
FirstName (txt)
MiddleName (txt)
Suffix (txt)
DOB (date)
Street (txt)
City (txt)
State (txt)
Zip (#)

EmployerName (txt)
Supervisor (txt)
Street (txt)
City (txt)
State (txt)
Zip (#)
LastUpdate (date)

SoldierPhone (#)
SoldierNOK (#)
SoldierAlt (#)
SoldierCell (#)
MomPhone (#)
DadPhone (#)
SpousePhone (#)
ChildPhone (#)
EmployerPhone (#)

Ok, now perhaps someone can at least see where I'm coming from...or perhaps I'm making a mountain out of a molehill...it's possible. But the hard thing to remember is that one soldier can have more than one mom/dad (with divorces and such), more than one child that may live at more than one address, as well as more than one spouse (former and current), and last, more than one employer.

I'm pulling my hair out...strand by painful strand...trying to figure this out. Can anyone, ANYONE please please please help?!? I'd be soooo appreciative of any advice!

 

[RuralGuy]

I'll start you off with one suggestion: unless you are going to perform math functions on a number, it should be a text field! (SSN) (Phone) (Zip) (Etc)

 

[Pat Hartman]

People are people and therefore, they should all be in the same table. Some of those people are soldiers and some are civililians so you should have a column that indicates that status.

To relate people, you need a relation table. The relation table contains three fields:
SoldierID, RelativeID, RelationshipCD
This allows you to handle soldiers married to each other as well as soldiers with a parent who is also military.

Addresses are addresses. So, the same thing applies. You would have one table for addresses and a relation table that relates people to addresses. If you want to handle multiple addresses for a person, you will need a third field in the table to identify the role of the address - mailing, residence, etc. You will need to take care here when changing an address that is shared by multiple people. Ask the user if the change applies to all residents or to only a specific one. You have to be able to handle one person moving out of the family home.

And finally, some of the military fields seem to require multiple values such as Meds and Allergies. These should be kept in separate tables. You may also need to keep start and end dates with them as well.

That should get you going. Post back and we'll continue.

 

[dp1981]

Um, huh???
From the looks of what you wrote, you definitely get it better than I do...because I'm confused. I've sat and looked at this several times over the past day, and I'm no less confused than when I started looking at it! I told you, the relationship part for me just doesn't compute well! Lol.

So, if I'm [not really but making an attempt at] understanding you, I should have tables similar to the following:

Tbl: People
Soldier
Spouse
Child
Mom
Dad
Emp Supervisor

Tbl: Status
Mil
Civ

Tbl: Relative
SoldierID
RelativeID
RelationshipID

Tbl: Meds
MedID
[unsure what I should put here in other fields...an actual Med like PCN??]

Tbl: Allergy
AllergyID
[same confusion, like mold? PCN?]

Tbl: Mob
MobID
Mobd
93
8286
Tags
FCP

Tbl: Addresses
AddyID
Soldier
Spouse
Child
Mom
Dad
Employer

Tbl: Phone
PhoneID
...[same theme as above]...

Is this kind of what you're saying I should do? So how do they get related? I hate building tables...grr...
Thanks for the help both of you. I truly do appreciate it!!!

People are people and therefore, they should all be in the same table. Some of those people are soldiers and some are civililians so you should have a column that indicates that status.

To relate people, you need a relation table. The relation table contains three fields:
SoldierID, RelativeID, RelationshipCD
This allows you to handle soldiers married to each other as well as soldiers with a parent who is also military.

Addresses are addresses. So, the same thing applies. You would have one table for addresses and a relation table that relates people to addresses. If you want to handle multiple addresses for a person, you will need a third field in the table to identify the role of the address - mailing, residence, etc. You will need to take care here when changing an address that is shared by multiple people. Ask the user if the change applies to all residents or to only a specific one. You have to be able to handle one person moving out of the family home.

And finally, some of the military fields seem to require multiple values such as Meds and Allergies. These should be kept in separate tables. You may also need to keep start and end dates with them as well.

That should get you going. Post back and we'll continue.

 

[Pat Hartman]

tblPerson:
PersonID (autonumber - primary key)
PersonTypeCode (txt)
LastName (txt)
FirstName (txt)
MiddleName (txt)
MaidenName (txt)
Suffix (txt)
SSN (txt)
DOB (date)
Street (txt)
City (txt)
State (txt)
Zip (txt)
Ht (#)
Wt (#)
DOR (date)
PEBD (date)
APFT (date)
Blood (txt)
Mobd (yes/no)
93 (date)
8286 (date)
Tags (yes/no)
Profile (#)
FCP (txt)
LastUpdateDate (date)
LastUpdateBy (txt)
I don't know wat a number of the fields are so I just left them. If any of them occur more than once for a person, you need to remove it to a separate table as I did with meds and allergies.

tblRelationshipType:
RelationshipTypeID (autonumber - primary key)
RelationshipType (txt) - Mother, Father, Daughter, Spouse, etc.
LastUpdateDate (date)
LastUpdateBy (txt)

tblRelationships:
SoldierID (pk field1, foreign key to tblPerson)
PersonID (pk field2, foreign key to tblPerson)
RelationshipTypeID (pk field3, foreign key to tblRelationshipType)
LastUpdateDate (date)
LastUpdateBy (txt)

tblAddress:
AddressID (Autonumber - PK)
Addr1
Addr2
City
State
Zip
Country
LastUpdateDate (date)
LastUpdateBy (txt)

tblAddressReference:
PersonID (pk field1, fk to tblPerson)
AddressID (pk field2, fk to tblAddress)
AddressType (txt) - Mailing, Residence, etc.

tblMeds:
MedsID (autonumber, PK)
PersonID (fk to tblPerson)
MedName (you could make a table of meds and make this a fk to that table)
StartDate
EndDate
Dosage

tblAllergy:
AllergyID (autonumber, PK)
PersonID (fk to tblPerson)
AllergyName


Person table contains ALL people in your universe. The Relationship table defines their relationship to each other. The Address table defines all addresses in your universe. The Address Reference table defines the relationships of addresses to people. The same address can be used as both mailing and residence and the same address can be residence for multiple people. this structure minimizes the potential for error when a family moves. The address changes for all family members by changing a single record.

Try making tables similar to address and address reference to handle contact numbers such as cell phones, pagers, email addresses, etc.

Medications and Allergies occur multiple times for a person so they belong in separate tables.

Play with this for a while and see where you get.

 

[Mike375]

Based on your last post Meds and Allergies are causing confusion. They also serve to illustrate scenarios for numbers of fields required in any table.

Allergies are no different to back problems, high blood pressure, anxiety etc. They are all health issues. Medications are a treatment. Treatments could also include chiropractor, physio, relaxation therapy, diet, exerise program etc.

What is the dose of medication......amount and fequency. Are they presciption or over the counter. Start date, change dose and/or frequency date, finish date etc.

Health issues might be the driver for this table. A health issue does not automatically mean there is medication. A health issue will always mean there is a treatement and that treatment could mean doing nothing. However, medication will always mean there is a health issue.

If he takes Valium is it for anxiety or back problems or both. If health issues were to be the driver for an additional desciption of the soldier then this Valium scenario would be two records. However, if medication was to be the driver then this scenario would probably be one record.

The point of the above being that you need to write down what you need and establish the driver for the table and then the other fields are descriptions of the driver field which in turn is a description of the soldier.

Fields you have for description can depend on who uses the data base. Take address as an example. For business address I also include a field for general locality because sometimes the data base is being used by someone who is not real familiar with Sydney and in a particular traffic/driving times. Thus my locality description does not really match the distances a map of Sydney would indicate but rather driving times/traffic problems etc.

As a general rule it is a whole lot easier to bring data together from what might be unnecessary fields than is it to break up data from one field and get the differents bits of data into individual fields. The same applies to number of records. If we take Meds/Health Issues as an example then having Health Issue as the driver will mean more records than if we have Medications as the driver. However, it will be a lot easier to change from Health Issues to Medications being the driver.

 

[dp1981]

ok, i think i finally got it (fingers crossed!!!). Can someone look at this to see if I'm on the right track maybe? You guys have been SOOOOO helpful so far and I never would have gotten this far without you! So I just want to make sure I did it right if someone has a second to double-check it for me.

Thanks again!!!