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
|
|||
|
|||
Primary Keys & Relationships
What are the advantages of using a Primary Key exactly AND/OR Relationships?
I'm trying to keep a table updated (with duplicates) as the info is entered via a form. The db is to keep track of different Companies that would be recommended verses not recommended for whatever reason. Each Company has it's own job # yet many different companies may have the same job # do to working on the same project. I really don't know where to go with this... I'm new to database design so any help would be very much appreiciated! Thank you! |
#2
|
|||
|
|||
=?Utf-8?B?bmFpdmVwcm9ncmFtbWVy?=
wrote in : What are the advantages of using a Primary Key exactly AND/OR Relationships? The last forty years of software database development is based on R theory, which itself is based on set theory; and says that proper table and relationship structure is the only way to guarantee the accuracy and integrity of your data. For further information, drop into any university bookshop and look for titles about data structures, relational database design etc. I really don't know where to go with this... I'm new to database design so any help would be very much appreiciated! This is probably not the place to shadow a complete design process -- this really includes the requirements analysis, data entities, entity life history, and so on and so on. If what you really need is a quick- and-dirty list manager, with the ability to sort, select, partition, and search then it is very hard to beat something like MS Excel, and MS Access certainly won't match it. On the other hand, if you do want to find out about db design then you have an exciting and (according to some sources!) rewarding learning curve ahead of you. Hanging around here will certainly give you some ideas for specific problems, but I guess you really need to start with a class or at the least a well-informed mentor to guide you. Hope that helps Tim F |
#3
|
|||
|
|||
Primary keys are important because they provide the database engine with a
way to uniquely identify a row in a table. If it can't do this, it will not let you delete a duplicate row from the table since it cannot identify precisely which of the duplicate rows you are asking to delete. They are also necessary when defining referential integrity since Access analyzes the primary keys of the related tables to determine the cardinality (1-m, 1-1) of the related tables. RI can only be enforced if Access (Jet) can accurately determine the cardinality of the relationship. PKs may be natural or artificial. Autonumbers are artificial. Keys that are made up of 1 or more columns of data that uniquely identify a row are natural keys. In your description it sounds like the combination of company and job would be the natural candidate keys. Relationships are defined specifically to enforce referential integrity. If you don't intend to enforce RI, there is no need to define a relationship in an Access database. Once you have PKs defined and create a relationship in which you check the enforce RI option, you will be presented with the options to Cascade Delete and Cascade Update. Cascade Delete is very useful. For example in hierarchical relationships such as Order - OrderDetails, the OrderDetails rows have no meaning except within the context of an Order so if you delete an Order, you would always want to delete its child OrderDetails. Cascade delete will automatically delete the OrderDetails rows when their parent Order is deleted. If you did not enforce RI or did not choose Cascade Delete, you would need to take care of deleting the OrderDetails yourself either with a query or a code loop. Be careful to not choose Cascade Delete in other types of relationships. For example, if you create a relationship between the Employee table and the SexCode table, you don't want to delete all the male employees if someone tries to delete the male sex code! In this case, since Cascade Delete is not specified, RI will tell you that you cannot delete the record from the SexCode table because there are related records in the Employee table. So in this case RI protected you from an accident. I could go on, but you really need to do some reading on the subject. Access supports multi-field keys so if one field can not be used to uniquely identify a row, you can choose up to 10 fields that in combination will uniquely define a row. To create a multi-field key, select the first column and while holding down the cntl key click and select up to nine more. When all the necessary fields are selected, press the key icon on the toolbar. Many people prefer to use autonumbers as their primary key when the natural key of a table exceeds one column. If you choose to use an autonumber primary key, you would then define a unique multi-field index to enforce the business rule that each company can only be associated with a given job number once. To create a multi-field unique index, open the index dialog box. Enter a name for the index and choose the first column. Select unique and ascending. then on the next line of the form, skip over the index name field and just select the next column. Since the index name is empty, Access will know that this column is part of the previously defined index. Indexes support up to 10 columns. "naiveprogrammer" wrote in message ... What are the advantages of using a Primary Key exactly AND/OR Relationships? I'm trying to keep a table updated (with duplicates) as the info is entered via a form. The db is to keep track of different Companies that would be recommended verses not recommended for whatever reason. Each Company has it's own job # yet many different companies may have the same job # do to working on the same project. I really don't know where to go with this... I'm new to database design so any help would be very much appreiciated! Thank you! |
#4
|
|||
|
|||
Wow! I can't say enough how wonderful ALL the info you have all contributed
is to me. Everything you all have said has really shed a lot of light on my learning about PK & Relationships as well as a great foundation of learning for Access. Really Thanks guys! "BruceM" wrote: To Pat Hartman's thoughtful and thorough response I will add just a few points. I think it helps if from the beginning you think of rows in a table as records, and columns as fields. They look like rows and columns in a spreadsheet, but they function very differently. A record is a collection of fields. When you look at a table it is sorted by some field or other, which you can change if it is more convenient, but the order in which they appear has no effect on the way the database performs. If the records need to be in order (by name or whatever) you need to use a query (or a sort order in a report). Working directly with tables is for the database designer/administrator only, not for the users. There is some disagreement about natural primary keys vs. autonumbers, but I will say that if you are just getting started an autonumber is a good way to go. Remember that an autonumber will almost inevitably have gaps in the sequence. If you move to a new record it is assigned a new autonumber primary key. If you move to the previous record, then forward again, the new record will be assigned a different autonumber than it was the first time. An autonumber is not for you to use as an invoice number or anything like that, since you cannot really control it. It is for the database's purposes only. You will use it to establish relationships, but you should not be concerned about what the actual number is. "Pat Hartman" wrote: Primary keys are important because they provide the database engine with a way to uniquely identify a row in a table. If it can't do this, it will not let you delete a duplicate row from the table since it cannot identify precisely which of the duplicate rows you are asking to delete. They are also necessary when defining referential integrity since Access analyzes the primary keys of the related tables to determine the cardinality (1-m, 1-1) of the related tables. RI can only be enforced if Access (Jet) can accurately determine the cardinality of the relationship. PKs may be natural or artificial. Autonumbers are artificial. Keys that are made up of 1 or more columns of data that uniquely identify a row are natural keys. In your description it sounds like the combination of company and job would be the natural candidate keys. Relationships are defined specifically to enforce referential integrity. If you don't intend to enforce RI, there is no need to define a relationship in an Access database. Once you have PKs defined and create a relationship in which you check the enforce RI option, you will be presented with the options to Cascade Delete and Cascade Update. Cascade Delete is very useful. For example in hierarchical relationships such as Order - OrderDetails, the OrderDetails rows have no meaning except within the context of an Order so if you delete an Order, you would always want to delete its child OrderDetails. Cascade delete will automatically delete the OrderDetails rows when their parent Order is deleted. If you did not enforce RI or did not choose Cascade Delete, you would need to take care of deleting the OrderDetails yourself either with a query or a code loop. Be careful to not choose Cascade Delete in other types of relationships. For example, if you create a relationship between the Employee table and the SexCode table, you don't want to delete all the male employees if someone tries to delete the male sex code! In this case, since Cascade Delete is not specified, RI will tell you that you cannot delete the record from the SexCode table because there are related records in the Employee table. So in this case RI protected you from an accident. I could go on, but you really need to do some reading on the subject. Access supports multi-field keys so if one field can not be used to uniquely identify a row, you can choose up to 10 fields that in combination will uniquely define a row. To create a multi-field key, select the first column and while holding down the cntl key click and select up to nine more. When all the necessary fields are selected, press the key icon on the toolbar. Many people prefer to use autonumbers as their primary key when the natural key of a table exceeds one column. If you choose to use an autonumber primary key, you would then define a unique multi-field index to enforce the business rule that each company can only be associated with a given job number once. To create a multi-field unique index, open the index dialog box. Enter a name for the index and choose the first column. Select unique and ascending. then on the next line of the form, skip over the index name field and just select the next column. Since the index name is empty, Access will know that this column is part of the previously defined index. Indexes support up to 10 columns. "naiveprogrammer" wrote in message ... What are the advantages of using a Primary Key exactly AND/OR Relationships? I'm trying to keep a table updated (with duplicates) as the info is entered via a form. The db is to keep track of different Companies that would be recommended verses not recommended for whatever reason. Each Company has it's own job # yet many different companies may have the same job # do to working on the same project. I really don't know where to go with this... I'm new to database design so any help would be very much appreiciated! Thank you! |
#5
|
|||
|
|||
To Pat Hartman's thoughtful and thorough response I will add just a few
points. I think it helps if from the beginning you think of rows in a table as records, and columns as fields. They look like rows and columns in a spreadsheet, but they function very differently. A record is a collection of fields. When you look at a table it is sorted by some field or other, which you can change if it is more convenient, but the order in which they appear has no effect on the way the database performs. If the records need to be in order (by name or whatever) you need to use a query (or a sort order in a report). Working directly with tables is for the database designer/administrator only, not for the users. There is some disagreement about natural primary keys vs. autonumbers, but I will say that if you are just getting started an autonumber is a good way to go. Remember that an autonumber will almost inevitably have gaps in the sequence. If you move to a new record it is assigned a new autonumber primary key. If you move to the previous record, then forward again, the new record will be assigned a different autonumber than it was the first time. An autonumber is not for you to use as an invoice number or anything like that, since you cannot really control it. It is for the database's purposes only. You will use it to establish relationships, but you should not be concerned about what the actual number is. "Pat Hartman" wrote: Primary keys are important because they provide the database engine with a way to uniquely identify a row in a table. If it can't do this, it will not let you delete a duplicate row from the table since it cannot identify precisely which of the duplicate rows you are asking to delete. They are also necessary when defining referential integrity since Access analyzes the primary keys of the related tables to determine the cardinality (1-m, 1-1) of the related tables. RI can only be enforced if Access (Jet) can accurately determine the cardinality of the relationship. PKs may be natural or artificial. Autonumbers are artificial. Keys that are made up of 1 or more columns of data that uniquely identify a row are natural keys. In your description it sounds like the combination of company and job would be the natural candidate keys. Relationships are defined specifically to enforce referential integrity. If you don't intend to enforce RI, there is no need to define a relationship in an Access database. Once you have PKs defined and create a relationship in which you check the enforce RI option, you will be presented with the options to Cascade Delete and Cascade Update. Cascade Delete is very useful. For example in hierarchical relationships such as Order - OrderDetails, the OrderDetails rows have no meaning except within the context of an Order so if you delete an Order, you would always want to delete its child OrderDetails. Cascade delete will automatically delete the OrderDetails rows when their parent Order is deleted. If you did not enforce RI or did not choose Cascade Delete, you would need to take care of deleting the OrderDetails yourself either with a query or a code loop. Be careful to not choose Cascade Delete in other types of relationships. For example, if you create a relationship between the Employee table and the SexCode table, you don't want to delete all the male employees if someone tries to delete the male sex code! In this case, since Cascade Delete is not specified, RI will tell you that you cannot delete the record from the SexCode table because there are related records in the Employee table. So in this case RI protected you from an accident. I could go on, but you really need to do some reading on the subject. Access supports multi-field keys so if one field can not be used to uniquely identify a row, you can choose up to 10 fields that in combination will uniquely define a row. To create a multi-field key, select the first column and while holding down the cntl key click and select up to nine more. When all the necessary fields are selected, press the key icon on the toolbar. Many people prefer to use autonumbers as their primary key when the natural key of a table exceeds one column. If you choose to use an autonumber primary key, you would then define a unique multi-field index to enforce the business rule that each company can only be associated with a given job number once. To create a multi-field unique index, open the index dialog box. Enter a name for the index and choose the first column. Select unique and ascending. then on the next line of the form, skip over the index name field and just select the next column. Since the index name is empty, Access will know that this column is part of the previously defined index. Indexes support up to 10 columns. "naiveprogrammer" wrote in message ... What are the advantages of using a Primary Key exactly AND/OR Relationships? I'm trying to keep a table updated (with duplicates) as the info is entered via a form. The db is to keep track of different Companies that would be recommended verses not recommended for whatever reason. Each Company has it's own job # yet many different companies may have the same job # do to working on the same project. I really don't know where to go with this... I'm new to database design so any help would be very much appreiciated! Thank you! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autonumbering Primary Keys | CC | New Users | 15 | April 14th, 2005 09:42 AM |
Are Primary keys needed in child tables? | Nevie and Phil | Database Design | 2 | January 16th, 2005 07:23 AM |
Indexes, Primary Keys and Relationships in MS Access | Naveen | Database Design | 4 | August 12th, 2004 07:17 PM |
Primary Keys and Relationships | TR | Database Design | 2 | May 23rd, 2004 03:19 AM |