If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database design help - HELP!
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 |
#2
|
|||
|
|||
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" wrote: 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 |
#3
|
|||
|
|||
On Wed, 12 Jan 2005 05:51:02 -0800, BruceM
wrote: 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 |
#4
|
|||
|
|||
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. "Charles" wrote: On Wed, 12 Jan 2005 05:51:02 -0800, BruceM wrote: 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 |
#5
|
|||
|
|||
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. "BruceM" wrote: 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. "Charles" wrote: On Wed, 12 Jan 2005 05:51:02 -0800, BruceM wrote: 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 |
#6
|
|||
|
|||
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. On Mon, 17 Jan 2005 06:09:02 -0800, BruceM wrote: 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. |
#7
|
|||
|
|||
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" wrote: 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. On Mon, 17 Jan 2005 06:09:02 -0800, BruceM wrote: 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. |
#8
|
|||
|
|||
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 On Mon, 17 Jan 2005 06:09:02 -0800, BruceM wrote: |
#9
|
|||
|
|||
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. "Charles" wrote: 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 On Mon, 17 Jan 2005 06:09:02 -0800, BruceM wrote: |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Free Access Training | Timboo | New Users | 8 | August 17th, 2005 05:58 PM |
I need help with my design Database Requirements.xls (01/01) | Database Design | 2 | December 7th, 2004 01:32 PM | |
You do not have exclusive access... ERROR | Robin | General Discussion | 1 | July 6th, 2004 01:18 AM |
Form Design with tracking into database | Alicat21 | Worksheet Functions | 1 | June 10th, 2004 12:00 AM |
database design basic help | als0107 | Database Design | 3 | May 6th, 2004 07:26 PM |