Database design help - HELP!

[Charles]

I have a flat database that will not do.. and need to make it as
relational as I possibly can. This is for a non-profit organization,
and deals with coal miners that populated the area in the late 1800 to
mid 1900's.

Sorry if this format gets screwed up. Details: This is an Access 2003
database running on windows XP.

My structure is as follows:

Miner Information: Duplicate names ARE allowed.

TBL_MINER

MINER_ID Unique ID for each miner. PRIMARY KEY
SEQUENCE_NO Sequence the miners are entered into the database
ETCHING_NO Number assigned to each miner to sequence the
names for etching
LAST_NAME
FIRST_NAME
MIDDLE_NAME
DOB
PLACE_OF_BIRTH
AGE
ETHNIC BACKGROUND
DIED IN MINE
INJURED IN MINE


TBL_MINES

MINES_WORKED What the mines individual worked in. An individual can
work in multiple mines


TBL_SPOUCE

WIFE_NAME
WIFE_DOB
WIFE_AGE

Source information, where the information was obtained. There can be
multiple sources of information for a single miner, such as a paid
submission, accident report, census, newspaper article.



TBL_SOURCE

SOURCE Where the information was obtained
SOURCE_YEAR Year of the information
SOURCE_LOCATION For instance, if the source was a census,
this would indicate the location of the person
when the information was gathered

PAGE More details about the source information. The
census details are used to locate the
information in the future.

ROW
SEARCH_PARAM Search criteria for location individual

ADDRESS Individuals home address, at the time the information
was gathered, if known
JOB_DESCRIPTION What the persons job function was.





Contributor information: People that have family members or
those that want to adopt can donate to the memorial.

TBL_CONTRIBUTOR

STORY Story submitted by contributor
ADOPTED_MINER Contributions made by people without a
specific miner to contribute to.
PAID True/False if the miner had been paid
for
FAMILY/CONTRIBUTOR/SPONSOR Contributor Name or information
CONTACT Contact person
ADDRESS
CITY
STATE
ZIP+4
TELEPHONE
CONTACT_EMAIL
DEDICATED TO
CONTRIBUTION Dollar amount submitted

 

[Guest]

When describing a database it is usually best to give table names (as you
have done), to identify each table's primary key, to identify foreign keys,
and to describe table relationships. When describing a table you need only
say something like:
MINER_ID (autonumber primary key)
MINER_NAME, etc.
Middle name, last name, DOB etc. that are not relevant to general design
only make the question harder to understand. By the way, what is the
question? Are you asking for comments on the design?
You seem to be on the right general track, but I don't have much sense of
how this all fits together. A few thoughts: Each miner can work at many
mines, and each mine can have many miners working there, making it a many to
many relationship. You will need a junction table to resolve that.
tblMinerMine (or whatever) would contain its own primary key, plus MINER_ID
and MINE_ID as foreign keys, each related to the primary key from TBL_MINER
and TBL_MINE.
You may find it simplest to have SEQUENCE_NO as either an incremented value
or maybe a time stamp, which you could get by setting the default value to
NOW(). Details available if you're interested.
Does Source relate just to miners, or to mines as well (will it be
associated with more than one table)?
You may want a contributions table to allow for more than one donation by an
individual. Also, as a general rule of table design you should be able to
describe the tables function in one sentence without using the word "and".
To elaborate a bit, name and address is OK because it is miner information or
contributor information; Contributor and amount don't go together as well.
If you are allowing for a miner having more than one spouse during his life
you may want to allow for each spouse to be married to more than one miner
over the course of her life. That would mean a junction table between
TBL_MINER and TBL_SPOUSE. Were women ever miners? Could a miner be married
to another miner? Also, could somebody be both a miner and a contributor, or
a spouse and a contributor? If yes to either, you may want a Personnel table
with Yes/No fields for Miner, Spouse, Contributor, or something like that
rather than individual tables for each. The thing you want to avoid is
having to enter identical personal information in multiple tables.

 

[Charles]

When describing a database it is usually best to give table names (as you
have done), to identify each table's primary key, to identify foreign keys,
and to describe table relationships. When describing a table you need only
say something like:

MINER_ID (autonumber primary key)
MINER_NAME, etc.

Middle name, last name, DOB etc. that are not relevant to general design
only make the question harder to understand. By the way, what is the
question? Are you asking for comments on the design?

Yes, in general the design is what I am looking for verification on..
I have had some database experience but there are too many unanswered
questions.

What I need to do is tie it all together... You hit on several issues,
which I will cover as I proceed through this message. If you are
interested, please read on!

You seem to be on the right general track, but I don't have much sense of
how this all fits together. A few thoughts:

Each miner can work at many mines
Each mine can have many miners working there, making it a many to
many relationship.

You will need a junction table to resolve that.

This junction is where I have trouble, and my inexperience with Access
is killing me. I think I understand the need to have "keys" in each
table that can be used to tie each table together, but this is vague.
Also.. when al of the tables are tied together, Data entry and
maintenance elude me!

tblMinerMine (or whatever) would contain its own primary key, plus MINER_ID
and MINE_ID as foreign keys, each related to the primary key from TBL_MINER
and TBL_MINE.

You may find it simplest to have SEQUENCE_NO as either an incremented value
or maybe a time stamp, which you could get by setting the default value to
NOW(). Details available if you're interested.

I'm interested.. but clueless!

Does Source relate just to miners, or to mines as well (will it be
associated with more than one table)?

Source is strictly "where" the information was obtained from, like:
Obituaries
Census reports
Surveys
Newspaper articles
Accident reports

The thing that is killing me is that in researching the miners and
their families, I can get more than one source. For instance, in 1920
a census report indicates "Miner" living at "Address".. and an
accident report can list the same person in some fashion... same miner
in two or more sources.

You may want a contributions table to allow for more than one donation by an
individual.

Great idea.

Also, as a general rule of table design you should be able to
describe the tables function in one sentence without using the word "and".
To elaborate a bit, name and address is OK because it is miner information or
contributor information; Contributor and amount don't go together as well.
If you are allowing for a miner having more than one spouse during his life
you may want to allow for each spouse to be married to more than one miner
over the course of her life. That would mean a junction table between
TBL_MINER and TBL_SPOUSE.

Another good idea

Were women ever miners? Could a miner be married
to another miner?

No... in that ear there were no women miners that we have come across.

also, could somebody be both a miner and a contributor, or
a spouse and a contributor? If yes to either, you may want a Personnel table
with Yes/No fields for Miner, Spouse, Contributor, or something like that
rather than individual tables for each. The thing you want to avoid is
having to enter identical personal information in multiple tables.

Right!

I really appreciate the assistance, and look forward to any and all
comments. This is a fantastic project for this area, many people from
all over the world were impacted by the coal mines, and the hardships
endured. - Charles



This is the database design as it now stands... if you would, insert
the "ties" that link the database together so I can see how it works!

tblMiner Miner Information: Duplicate
names ARE allowed.

MINER_ID Unique ID for each miner. PRIMARY KEY
ETCHING_NO Number assigned to each miner to
sequence the names for etching
LAST_NAME
FIRST_NAME
MIDDLE_NAME
DOB
PLACE_OF_BIRTH
AGE
ETHNIC BACKGROUND
DIED IN MINE
INJURED IN MINE

tblMines

MINES_WORKED What the mines individual worked in. An
individual can work in multiple mines


tblSpouce

WIFE_NAME
WIFE_DOB
WIFE_AGE


tblSource Source information, where the
information was obtained. There can
be multiple sources of information for
a single miner, such as a paid
submission, accident report, census,
newspaper article.

SOURCE Where the information was obtained
SOURCE_YEAR Year of the information
SOURCE_LOCATION For instance, if the source was a census, this
would indicate the location of the
person when
the information was gathered

PAGE More details about the source
information. The census details are
used to locate
the information in the future.
ROW
SEARCH_PARAM Search criteria for location individual

ADDRESS Individuals home address, at the time the
information was gathered, if known
JOB_DESCRIPTION What the persons job function was.


tblContributor Contributor information: People that
have family members or those that want
to adopt can donate to the memorial.


STORY Story submitted by contributor
ADOPTED_MINER Contributions made by people without a
specific miner to contribute to.
PAID True/False if the fees for having the
name etched has been paid for

FAMILY/CONTRIBUTOR/SPONSOR Contributor Name or information
CONTACT Contact person
ADDRESS
CITY
STATE
ZIP+4
TELEPHONE
CONTACT_EMAIL
DEDICATED TO
CONTRIBUTION Dollar amount submitted

 

[Guest]

Didn't mean to leave you hanging for so long. Things suddenly got very busy
here. I
will have to check this out from home. I will just say that a junction
table is a little hard to wrap one's mind around at first, but you will get
used to it quickly enough. Remember that with a one to many each miner could
work for many mines if you aren't concerned with also keeping a roster of who
works at a particular mine. Once you need to keep track of both the miner's
mines and the mine's miners you need a junction table. It contains its own
primary key (PK) and a foreign key (FK) to connect each record to both a mine
and a miner. A primary key is defined in table design view, but the foreign
key is just a field. You make it a foreign key by establishing a
relationship between it and the primary key (Tools > Relationships). See
Help for the mechanics of this. One caveat: if the PK is autonumber (in
table design) then the FK needs to be number. If the PK is something other
than autonumber (number would be the only other real choice unless you are
combining fields, and don't bother with that for now), the FK needs to be the
same data type.
Sounds like a great project, but it is fairly complex. You can get it, I'm
sure, but it will take some time.

 

[Guest]

Didn't get another chance to look at it until now. If I am repeating what
you already know, bear with me for a moment. A primary key (PK) is a
record's unique identifier. Each record in a table of names (miners,
employees, or whatever) could be identified by SS #, Employee #, etc. A SS #
is unique to a person, no matter what name changes, etc. the person
experiences. You can always identify that person and their payroll history
(for instance) by that number. Other types of records do not have an obvious
PK, so you need to assign one. Autonumber does that automatically. There is
some debate about whether autonumber is the best PK choice, but I think it
works quite well in many cases.
TblMines would contain the mine's name, location, and anything else specific
to the maine. TblMineRoster would have MineRosterID (PK), MinesID (foreign
key, or FK), MinerID (FK - more on this field shortly), and maybe start date,
end date, and anything else that is specific to that person working at that
mine. You would also have a miner table (tblMiners) containing each miner's
personal information. Or it could be tblCommunity, containing names of all
miners, spouses, contributors, etc., with check boxes (Yes/No fields) to
identify if the person if a miner, spouse, contributor, descendent, etc., and
whether they are still living. I'll keep it simple and stick with miners,
and call it tblMiners. Each record has MinerID (PK), FirstName, LastName,
DOB, etc.
If every miner works at just one mine, you could establish a one-to-many
relationship between MinesID in tblMines and tblMinesRoster. Let's say that
the PK for the first record in tblMines is 101. Each record in
tblMinesRoster with the FK 101 would be linked to that first record (and only
that record) in tblMines. A form/subform is the easiest way to manage this.
You do not need to actively assign 101 as the value of the FK field. The
relationship between the MinesID fields takes care of that.
You would similarly link MinerID to tblMinesRoster.
However, each miner can work at many mines, as well as each mine employing
many miners. TblMinesRoster will not work as described. You will not use it
at all. Instead, you will need tblMinerMines, with its own PK, the FKs from
tblMines and tblMiners, Start Date, etc. Establish relationships between the
PKs and the FKs as before. Make a subform (fsubMinerMines) based on
tblMinerMines in place of the one based on tblMinesRoster. Make a subform
exactly as you would a form. I use fsubFormName as a naming convention for
subforms. Use a combo box to select the miner names (you can use the wizard).
Make a new record in tblMines (using the form), and in the subform select
the names of the miners. When you are done, make a new record in tblMines.
Include some of the same miners in this record. Now if you use a report (or
another form) to view a miner's history you can see that a miner worked at
both mines.
Try setting up just this much for now, to see how it works. Use whatever
object and control names you like, but I think a naming convention is a great
help. Check Google for Access Naming Convention or something like that.
There are some standard systems.

 

[Charles]

Bruce, thank you for your assistance... I broke this down a bit to
digest it... and will look at and think about it before proceding... I
can see one point you made that had eluded me, that a table can
consist soley of a PK and multiple FK's (see tblMineRoster) acting as
a "junction" point for other tables...

I've attached the data dictionary (word 2003 document) to make it
easier to read.

Didn't get another chance to look at it until now. If I am repeating what
you already know, bear with me for a moment. A primary key (PK) is a
record's unique identifier. Each record in a table of names (miners,
employees, or whatever) could be identified by SS #, Employee #, etc. A SS #
is unique to a person, no matter what name changes, etc. the person
experiences. You can always identify that person and their payroll history
(for instance) by that number. Other types of records do not have an obvious
PK, so you need to assign one. Autonumber does that automatically. There is
some debate about whether autonumber is the best PK choice, but I think it
works quite well in many cases.

tblMines would contain:

mine name
mine location
anything else specific to the maine.

tblMineRoster would contain:

MineRosterID (PK)
MinesID (FK)
MinerID (FK - more on this field shortly)
and anything else that is specific to that person working at that
mine.

tblMiners - containing each miner's personal information
- or -
tblCommunity - containing names of all miners, spouses, contributors,
etc., with check boxes (Yes/No fields) to identify if the person if a
miner, spouse, contributor, descendent, etc., and whether they are
still living. I'll keep it simple and stick with miners,

tblMiners would contain:

MinerID (PK)
FirstName
LastName
DOB, etc.

If every miner works at just one mine, you could establish a
one-to-many relationship between MinesID in tblMines and
tblMinesRoster. Let's say that the PK for the first record in
tblMines is 101. Each record in tblMinesRoster with the FK 101 would
be linked to that first record (and only >that record) in tblMines.
A form/subform is the easiest way to manage this.

You do not need to actively assign 101 as the value of the FK field.
The relationship between the MinesID fields takes care of that.
You would similarly link MinerID to tblMinesRoster. However, each
miner can work at many mines, as well as each mine employing
many miners. TblMinesRoster will not work as described. You will not
use it at all. Instead, you will need tblMinerMines, with its own PK,
the FKs from tblMines and tblMiners, Start Date, etc.

Establish relationships between the PKs and the FKs as before. Make a
subform (fsubMinerMines) based on tblMinerMines in place of the one
based on tblMinesRoster. Make a subform exactly as you would a form.
I use fsubFormName as a naming convention for subforms. Use a combo
box to select the miner names (you can use the wizard). Make a new
record in tblMines (using the form), and in the subform select the
names of the miners. When you are done, make a new record in
tblMines. Include some of the same miners in this record. Now if you
use a report (or another form) to view a miner's history you can see
that a miner worked at both mines.

Try setting up just this much for now, to see how it works. Use
whatever object and control names you like, but I think a naming
convention is a great help. Check Google for Access Naming Convention
or something like that. There are some standard systems.

 

[Guest]

That point eluded me about junction tables for a time. Thanks to this group
I got it straightened out. If you were to include the start date and end
date or employment that would also go into the junction table, as it is
specific to a particular miner working at a particular mine.
I am a relative beginner to database design, and there are some points about
yours on which I may not be providing the best answer, such as the question
of how to handle people who are spouses, contributors, etc. in various
combinations. Upon reflection I think a single table for miners and
everybody else would not be the best approach, but it could make sense to
have tblCommunity include everybody except miners. That way a spouse could
also be included on a list of contributors without the need to enter the name
in separate tables (something to be avoided at all times unless there is a
very unusual and compelling situation). With Yes/No fields you could design
a query to return the names of all spouses, all contributors, all spouses who
are also contributors, and so forth. If an old miner could also be a
contributor you could add a Yes/No field to tblMiner, query the table, and
combine that with the list of contributors from tblCommunity. You will
probably want a tblContributions, since you don't want to include
contributions along with personal information. Also, it will enable you to
list any number of contributions for each contributor.
I would suggest experimenting a bit, then posting a new thread with what you
have so far and what you still need to do. I am not clear on whether you
attached a word document to this message, but as I am limited to the web
based newsreader I cannot get attachments. For that matter I am not sure if
that can be done at all directly through the newsgroup.
Good luck, and have fun. Sounds like a great project.

 

[Charles]

Thanks Bruce for you continued assistance... the message I posted
yesterday to you did not go through (didn't know no binaries here) I
was going to attach the data dictionary in a word document. I'll go
over this, break it down and get back...

Again, Thank you.
Charles

 

[Guest]

No need for the actual data, since the important thing right now is the
design and structure. I am only asking the same sorts of questions I wish
somebody had asked me in some of my earlier projects. A term I have heard
with table design is "entities". A table should contain information about a
single entity, such as miner, mine, contribution. Junction tables don't fit
as neatly into this way of thinking; MinerMine is a little harder to
visualize in that way, but the concept still holds. If you find yourself
entering the same data more than once (names, for instance) then you will
likely have difficulties later. That is why I asked if spouses can be
contributors, etc.