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
|
|||
|
|||
Understanding Primary Keys
table1
eID as long (autonumber) miscinfo as text moreinfo as text table2 eID as long? e2ID as long? yetmoreinfo as text Say I had multiple table2's for each table1. Would eID in table2 be set to the same value in table1.eID, and then, would that mean e2ID would be a unique key on it's own, labeled PK? Is this where Foreign Keys are? So table1.eID and table2.eID equal each other? I understand the reasoning for PK's - but having trouble understanding the concept and how exactly they relate to another table. =/ in the example relationship dbase at http://members.iinet.net.au/~allenbrowne/AppHuman.html (I found that in another post here in the group), it has GroupTypeID listed in tblGroup and tblGroupType. Say they're long autonumbers, do you defined tblGroupID as Long (Autonumber), or just as Long, and on Creation, set it to tblGroupType.GroupTypeID ? Then, if that were the case, how would you pull up all tblGroups in a form that were listed to tblGroupType.GroupTypeID? *wandering around in circles* DPHarr, hopeful database creator. |
#2
|
|||
|
|||
Whoa there, young feller. Take a deep breath. Now take another. I think
you need to step out of the circle and consider some basic tenets of relational databases. First: Access creates Relationships when you join tables in the Relationships window. It's up to you to develop & set up your db schema in such a way that relationships between tables are clearly understood by Access, and are therefore clearly defined (not of the dreaded "indeterminate" type) when created. Access then uses the relationships to find associated information in your db quickly and efficiently. As with anything, you have to start at the beginning. What you essentially have to do is chop your data down to their most atomic and logical components (aka "decomposing"), then figure out how to put them all back together again in meaningful ways. Ask yourself, what are the entities (real-life persons, places, things, or events that the db will keep track of) and their attributes (categories of related information relevent to each entity)? Entities & attributes = tables & fields. Once you have determined what tables and fields the db needs, then you have to set your primary keys (using one or more fields) to uniquely identify each record in a table. Then once the pk's are in place, you have to tell Access how to bring all the information together again using criteria that you will set when you query the db (e.g. "All orders for fiscal year 1999", "All streams in the Shenandoah River watershed", "All contributions by corporate donors greater than $10,000," etc.). To set up relationships between tables, add one table's pk to the other table (in which case it's known as a foreign key). To decide which table's pk should go where, you have to figure out how entities are related to each other: - In a one-to-many relationship (1:m), a record in Table A can have more than one matching record in Table B, but a record in Table B has no more than one matching record in table A. To set up this relationship, add the field(s) that make up the pk on the "one" side of the relationship to the table on the "many" side. Check out the Northwinds example database included with most versions of Access for an example of such a relationship between the Suppliers and Products tables; one supplier can supply more than one product, but each product has only one supplier. - In a many-to-many (m:n) relationship, Table A can have more than one matching record in Table B AND tblB can have more than one matching record in tblA. This type of relationship cannot be specified to Access as such; you have to break up a m:n relation into TWO 1:m relationships by using a junction or "resolver" table. Put the pk's from each of the two tables into the resolver table, which then acts as sort of a traffic cop between the other two tables. Again, refer to the Northwinds db for an example...the OrderDetails table resolves a m:n relationship between the Orders and Products tables (each Order can include one OR MORE Products, AND each Product can be ordered one OR MORE times). - One-to-one (1:1) relationships are rare, and generally (but not always) mean you need to rethink your table design. In this relationship type, tblA can have only one matching record in tblB, and tblB can have no more than one matching record in tblA. If entities are related 1:1, that usually (but not always) means their attributes can be combined in a single table. This MS support site contains links to many excellent resources on relational database design: http://support.microsoft.com/default...b;EN-US;289533 Now, using Allen’s donor db as an example: Take a look at the GroupTypes table. As the text explains, there can be more than one type of group making contributions. Here’s some example data to help you get your head around this idea: GrpTypeID GrpTypeName 1 Corporate 2 Nonprofit 3 Individual The TypeID is the pk for this table. Why not just put this information in tblGroups, you may ask? The answer is, this way the name of each group type only has to be typed in ONCE. To clarify this, here’s some dummy data from tblGroups: GrpID GrpName TypeID 1 IBM Corporate 2 Microsoft Corprate 3 Gen Motors Corpororate 4 PBS Nonprofit 5 NEH Non-profit 6 Joe Blow Individual 7 Jane Doe Indiv 8 Richard Roe Ind Compare this to: GrpID GrpName TypeID 1 IBM 1 2 Microsoft 1 3 Gen Motors 1 4 PBS 2 5 NEH 2 6 Joe Blow 3 7 Jane Doe 3 8 Richard Roe 3 You see where I’m going here? Users need never even see Type ID or GrpID. These key fields exist to uniquely identify each record in their respective tables AND to help Access keep track of related information in tables. But users “look up” the correct group type & select it from the data entry form (based on a query that includes both tblGroupTypes and tblGroups)…they “see” the Type Name, but Access stores the TypeID, and the user is never the wiser. This helps head off errors when entering data, and typos when attempting to retrieve data via queries (a query for all groups WHERE GrpTypeName=”Corporate” will return only 1 record from tblGroups; WHERE GrpTypeName=”Croprate” will return no records at all). Incidentally, the above example presumes a 1:m relationship between GrpTypes and Groups...group types may appear one OR MORE times in tblGroups, but each group represents ONE AND ONLY ONE Group Type. The TypeID field is an autonumber in tblGroupTypes, but a Number (Long) in tblGroups; you can’t join autonumber to autonumber Apologies for length, but I hope this helps you get handle on pk’s. If not, post back and I’ll try to explain it better. Good luck, LeAnne Khai wrote: table1 eID as long (autonumber) miscinfo as text moreinfo as text table2 eID as long? e2ID as long? yetmoreinfo as text Say I had multiple table2's for each table1. Would eID in table2 be set to the same value in table1.eID, and then, would that mean e2ID would be a unique key on it's own, labeled PK? Is this where Foreign Keys are? So table1.eID and table2.eID equal each other? I understand the reasoning for PK's - but having trouble understanding the concept and how exactly they relate to another table. =/ in the example relationship dbase at http://members.iinet.net.au/~allenbrowne/AppHuman.html (I found that in another post here in the group), it has GroupTypeID listed in tblGroup and tblGroupType. Say they're long autonumbers, do you defined tblGroupID as Long (Autonumber), or just as Long, and on Creation, set it to tblGroupType.GroupTypeID ? Then, if that were the case, how would you pull up all tblGroups in a form that were listed to tblGroupType.GroupTypeID? *wandering around in circles* DPHarr, hopeful database creator. |
#3
|
|||
|
|||
LeAnne, I've seen your stuff, and know you're heavily involved in the
newsgroups, and I just have to say - I love you! hehe. Having read all that below, and re-reading my original post, I have to ask one minor question. Each client can have multiple applications (so 1:m, right?) if eID% is a random autoincrement, when I add an application, do I have to make the ClientID = tblClient.eID or... ? do I have to type it in, or can I programattically assign it? So I can look up a client, then see all applications for that client? But then, on the other side, I can pull up an Application, and go the other way to see which client it belong to? (This is a much more simplified version of what I'm trying to do. hehe.) tblClient First$ Last$ eID% (PK) myApplication$ tblApplications clientID Date Amount ($000.00) AppID% (PK) -dpharr "LeAnne" wrote in message ... Whoa there, young feller. Take a deep breath. Now take another. I think you need to step out of the circle and consider some basic tenets of relational databases. First: Access creates Relationships when you join tables in the Relationships window. It's up to you to develop & set up your db schema in such a way that relationships between tables are clearly understood by Access, and are therefore clearly defined (not of the dreaded "indeterminate" type) when created. Access then uses the relationships to find associated information in your db quickly and efficiently. As with anything, you have to start at the beginning. What you essentially have to do is chop your data down to their most atomic and logical components (aka "decomposing"), then figure out how to put them all back together again in meaningful ways. Ask yourself, what are the entities (real-life persons, places, things, or events that the db will keep track of) and their attributes (categories of related information relevent to each entity)? Entities & attributes = tables & fields. Once you have determined what tables and fields the db needs, then you have to set your primary keys (using one or more fields) to uniquely identify each record in a table. Then once the pk's are in place, you have to tell Access how to bring all the information together again using criteria that you will set when you query the db (e.g. "All orders for fiscal year 1999", "All streams in the Shenandoah River watershed", "All contributions by corporate donors greater than $10,000," etc.). To set up relationships between tables, add one table's pk to the other table (in which case it's known as a foreign key). To decide which table's pk should go where, you have to figure out how entities are related to each other: - In a one-to-many relationship (1:m), a record in Table A can have more than one matching record in Table B, but a record in Table B has no more than one matching record in table A. To set up this relationship, add the field(s) that make up the pk on the "one" side of the relationship to the table on the "many" side. Check out the Northwinds example database included with most versions of Access for an example of such a relationship between the Suppliers and Products tables; one supplier can supply more than one product, but each product has only one supplier. - In a many-to-many (m:n) relationship, Table A can have more than one matching record in Table B AND tblB can have more than one matching record in tblA. This type of relationship cannot be specified to Access as such; you have to break up a m:n relation into TWO 1:m relationships by using a junction or "resolver" table. Put the pk's from each of the two tables into the resolver table, which then acts as sort of a traffic cop between the other two tables. Again, refer to the Northwinds db for an example...the OrderDetails table resolves a m:n relationship between the Orders and Products tables (each Order can include one OR MORE Products, AND each Product can be ordered one OR MORE times). - One-to-one (1:1) relationships are rare, and generally (but not always) mean you need to rethink your table design. In this relationship type, tblA can have only one matching record in tblB, and tblB can have no more than one matching record in tblA. If entities are related 1:1, that usually (but not always) means their attributes can be combined in a single table. This MS support site contains links to many excellent resources on relational database design: http://support.microsoft.com/default...b;EN-US;289533 Now, using Allen’s donor db as an example: Take a look at the GroupTypes table. As the text explains, there can be more than one type of group making contributions. Here’s some example data to help you get your head around this idea: GrpTypeID GrpTypeName 1 Corporate 2 Nonprofit 3 Individual The TypeID is the pk for this table. Why not just put this information in tblGroups, you may ask? The answer is, this way the name of each group type only has to be typed in ONCE. To clarify this, here’s some dummy data from tblGroups: GrpID GrpName TypeID 1 IBM Corporate 2 Microsoft Corprate 3 Gen Motors Corpororate 4 PBS Nonprofit 5 NEH Non-profit 6 Joe Blow Individual 7 Jane Doe Indiv 8 Richard Roe Ind Compare this to: GrpID GrpName TypeID 1 IBM 1 2 Microsoft 1 3 Gen Motors 1 4 PBS 2 5 NEH 2 6 Joe Blow 3 7 Jane Doe 3 8 Richard Roe 3 You see where I’m going here? Users need never even see Type ID or GrpID. These key fields exist to uniquely identify each record in their respective tables AND to help Access keep track of related information in tables. But users “look up” the correct group type & select it from the data entry form (based on a query that includes both tblGroupTypes and tblGroups)…they “see” the Type Name, but Access stores the TypeID, and the user is never the wiser. This helps head off errors when entering data, and typos when attempting to retrieve data via queries (a query for all groups WHERE GrpTypeName=”Corporate” will return only 1 record from tblGroups; WHERE GrpTypeName=”Croprate” will return no records at all). Incidentally, the above example presumes a 1:m relationship between GrpTypes and Groups...group types may appear one OR MORE times in tblGroups, but each group represents ONE AND ONLY ONE Group Type. The TypeID field is an autonumber in tblGroupTypes, but a Number (Long) in tblGroups; you can’t join autonumber to autonumber Apologies for length, but I hope this helps you get handle on pk’s. If not, post back and I’ll try to explain it better. Good luck, LeAnne Khai wrote: table1 eID as long (autonumber) miscinfo as text moreinfo as text table2 eID as long? e2ID as long? yetmoreinfo as text Say I had multiple table2's for each table1. Would eID in table2 be set to the same value in table1.eID, and then, would that mean e2ID would be a unique key on it's own, labeled PK? Is this where Foreign Keys are? So table1.eID and table2.eID equal each other? I understand the reasoning for PK's - but having trouble understanding the concept and how exactly they relate to another table. =/ in the example relationship dbase at http://members.iinet.net.au/~allenbrowne/AppHuman.html (I found that in another post here in the group), it has GroupTypeID listed in tblGroup and tblGroupType. Say they're long autonumbers, do you defined tblGroupID as Long (Autonumber), or just as Long, and on Creation, set it to tblGroupType.GroupTypeID ? Then, if that were the case, how would you pull up all tblGroups in a form that were listed to tblGroupType.GroupTypeID? *wandering around in circles* DPHarr, hopeful database creator. |
#4
|
|||
|
|||
Hi Khai,
Khai wrote: LeAnne, I've seen your stuff, and know you're heavily involved in the newsgroups, and I just have to say - I love you! hehe. Gee, thanks...blushing Having read all that below, and re-reading my original post, I have to ask one minor question. Each client can have multiple applications (so 1:m, right?) if eID% is a random autoincrement, when I add an application, do I have to make the ClientID = tblClient.eID or... ? do I have to type it in, or can I programattically assign it? So I can look up a client, then see all applications for that client? But then, on the other side, I can pull up an Application, and go the other way to see which client it belong to? (This is a much more simplified version of what I'm trying to do. hehe.) tblClient First$ Last$ eID% (PK) myApplication$ tblApplications clientID Date Amount ($000.00) AppID% (PK) -dpharr Ok, if each client can apply multiple times, but each specific application refers to one and only one client, then yep, it's a 1:m relationship. And yep, you need some linking field (or combination of fields) to clarify that relationship to Access. If eID is the same thing as ClientID (if so, this seems confusing to me...I prefer keeping the fieldnames the same for clarity), and eID is an incrementing autonumber in tblClients, then ClientID would be the linking field, the foreign key in tblApplications, with field datatype Number (Long). tbltblClients would be joined one-to-many to tblApps on eID=ClientID. You don't have to do this "every time you add an application;" the relationship is (hopefully) already defined. Then by including both of these tables in a query, you can ask Access to "Show me a list of client names and all of their associated applications" like so: SELECT Lastname, FirstName, AppID, other fields as needed FROM tblClients INNER JOIN tblApplications ON tblClients.eID=tblApplications.ClientID; or, "Show me the name of the client associated with Application 42" like this: SELECT AppID, FirstName, LastName FROM tblClients INNER JOIN tblApplications ON tblClients.eID=tblApplications.ClientID WHERE tblApplications.AppID=42; Note that if you have already specified the relationships correctly to Access, an INNER JOIN works fine. Having said all that, I'm unclear as to why you have the myApplications field in tblClients. Remember, tables are groups of RELATED attributes. tblClients should contain ONLY info pertinent to Clients (e.g. FirstName, LastName {I'd avoid using "First" and "Last" as fieldnames as they are reserved words), Street Address, City, State, Zip, OfficePhone, CellPhone, FaxNum, etc.). One thing more...you ARE using a form to input your data, right??? Data entry via tables is generally considered A Bad Idea. If you don't have a data entry form to act as the user interface to your tables, then you will have to first enter the correct data in tblClients, let Access assign the eID using the counter, and then remember AND manually type that number into tblApplications each time you add another record to tblApplications. I recommend you search Help for the topics "Forms: What they are and how they work" and "Ways to work with data in a form" to find out more about creating a data entry form which you can use to enter data into multiple tables. hth, LeAnne "LeAnne" wrote in message ... Whoa there, young feller. Take a deep breath. Now take another. I think you need to step out of the circle and consider some basic tenets of relational databases. First: Access creates Relationships when you join tables in the Relationships window. It's up to you to develop & set up your db schema in such a way that relationships between tables are clearly understood by Access, and are therefore clearly defined (not of the dreaded "indeterminate" type) when created. Access then uses the relationships to find associated information in your db quickly and efficiently. As with anything, you have to start at the beginning. What you essentially have to do is chop your data down to their most atomic and logical components (aka "decomposing"), then figure out how to put them all back together again in meaningful ways. Ask yourself, what are the entities (real-life persons, places, things, or events that the db will keep track of) and their attributes (categories of related information relevent to each entity)? Entities & attributes = tables & fields. Once you have determined what tables and fields the db needs, then you have to set your primary keys (using one or more fields) to uniquely identify each record in a table. Then once the pk's are in place, you have to tell Access how to bring all the information together again using criteria that you will set when you query the db (e.g. "All orders for fiscal year 1999", "All streams in the Shenandoah River watershed", "All contributions by corporate donors greater than $10,000," etc.). To set up relationships between tables, add one table's pk to the other table (in which case it's known as a foreign key). To decide which table's pk should go where, you have to figure out how entities are related to each other: - In a one-to-many relationship (1:m), a record in Table A can have more than one matching record in Table B, but a record in Table B has no more than one matching record in table A. To set up this relationship, add the field(s) that make up the pk on the "one" side of the relationship to the table on the "many" side. Check out the Northwinds example database included with most versions of Access for an example of such a relationship between the Suppliers and Products tables; one supplier can supply more than one product, but each product has only one supplier. - In a many-to-many (m:n) relationship, Table A can have more than one matching record in Table B AND tblB can have more than one matching record in tblA. This type of relationship cannot be specified to Access as such; you have to break up a m:n relation into TWO 1:m relationships by using a junction or "resolver" table. Put the pk's from each of the two tables into the resolver table, which then acts as sort of a traffic cop between the other two tables. Again, refer to the Northwinds db for an example...the OrderDetails table resolves a m:n relationship between the Orders and Products tables (each Order can include one OR MORE Products, AND each Product can be ordered one OR MORE times). - One-to-one (1:1) relationships are rare, and generally (but not always) mean you need to rethink your table design. In this relationship type, tblA can have only one matching record in tblB, and tblB can have no more than one matching record in tblA. If entities are related 1:1, that usually (but not always) means their attributes can be combined in a single table. This MS support site contains links to many excellent resources on relational database design: http://support.microsoft.com/default...b;EN-US;289533 Now, using Allen’s donor db as an example: Take a look at the GroupTypes table. As the text explains, there can be more than one type of group making contributions. Here’s some example data to help you get your head around this idea: GrpTypeID GrpTypeName 1 Corporate 2 Nonprofit 3 Individual The TypeID is the pk for this table. Why not just put this information in tblGroups, you may ask? The answer is, this way the name of each group type only has to be typed in ONCE. To clarify this, here’s some dummy data from tblGroups: GrpID GrpName TypeID 1 IBM Corporate 2 Microsoft Corprate 3 Gen Motors Corpororate 4 PBS Nonprofit 5 NEH Non-profit 6 Joe Blow Individual 7 Jane Doe Indiv 8 Richard Roe Ind Compare this to: GrpID GrpName TypeID 1 IBM 1 2 Microsoft 1 3 Gen Motors 1 4 PBS 2 5 NEH 2 6 Joe Blow 3 7 Jane Doe 3 8 Richard Roe 3 You see where I’m going here? Users need never even see Type ID or GrpID. These key fields exist to uniquely identify each record in their respective tables AND to help Access keep track of related information in tables. But users “look up” the correct group type & select it from the data entry form (based on a query that includes both tblGroupTypes and tblGroups)…they “see” the Type Name, but Access stores the TypeID, and the user is never the wiser. This helps head off errors when entering data, and typos when attempting to retrieve data via queries (a query for all groups WHERE GrpTypeName=”Corporate” will return only 1 record from tblGroups; WHERE GrpTypeName=”Croprate” will return no records at all). Incidentally, the above example presumes a 1:m relationship between GrpTypes and Groups...group types may appear one OR MORE times in tblGroups, but each group represents ONE AND ONLY ONE Group Type. The TypeID field is an autonumber in tblGroupTypes, but a Number (Long) in tblGroups; you can’t join autonumber to autonumber Apologies for length, but I hope this helps you get handle on pk’s. If not, post back and I’ll try to explain it better. Good luck, LeAnne Khai wrote: table1 eID as long (autonumber) miscinfo as text moreinfo as text table2 eID as long? e2ID as long? yetmoreinfo as text Say I had multiple table2's for each table1. Would eID in table2 be set to the same value in table1.eID, and then, would that mean e2ID would be a unique key on it's own, labeled PK? Is this where Foreign Keys are? So table1.eID and table2.eID equal each other? I understand the reasoning for PK's - but having trouble understanding the concept and how exactly they relate to another table. =/ in the example relationship dbase at http://members.iinet.net.au/~allenbrowne/AppHuman.html (I found that in another post here in the group), it has GroupTypeID listed in tblGroup and tblGroupType. Say they're long autonumbers, do you defined tblGroupID as Long (Autonumber), or just as Long, and on Creation, set it to tblGroupType.GroupTypeID ? Then, if that were the case, how would you pull up all tblGroups in a form that were listed to tblGroupType.GroupTypeID? *wandering around in circles* DPHarr, hopeful database creator. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Are Primary keys needed in child tables? | Nevie and Phil | Database Design | 2 | January 16th, 2005 08:23 AM |
Logical question on primary keys... | Access rookie | Database Design | 4 | January 8th, 2005 12:26 PM |
Using Primary Keys | Jodie | General Discussion | 1 | July 14th, 2004 08:49 PM |
Choosing Primary and Foreign Keys | A.V.H | New Users | 8 | May 23rd, 2004 09:12 PM |