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 |
#11
|
|||
|
|||
Note that ms-access "knows" if it is a one to one, or a one to "many" based
on the fact that the foreign key in the child table has a unique index setting (if you only allow ONE value of the same type...then it has to be a one to one. If you remove the duplicates allowed, then obviously you can have "many"). By the way, you are correct in that the child tables do NOT need a autonumber primary key. However, you REALLY REALLY REALLY REALLY shold put that autonumber field in. The reason for this is if later on you do want to relate a table to that child table, then you can without having to add that autonumber collum. Furhter, to get the last reocrd of a child table is VERY common question (last inoive, last order, last whatever!!). If you do NOT add a autonumber field, then grabbing the last invoice for a custom canbe difficlet. Adding a autonumber makes that reocrd UNIQUE, and even if you don't relatae data to that table, you should always as a rule have a UNIQUE way of working with a particlar reocrd. (this will bite you when you got code, or just about anything that needs to manipulate those child reocrds. So, a primary key is not only for reoanlships, but just for you to keep yourself sane, and allow yo to write code etc. that can work with a SINGLE reocrd in a table. So, don't bother to remove those autonubmer collums, as they will come to good use in the future. For example: If you have a autonumber, then to get the custoemrs last inoivce you can use: tblCustomer tblInvoice SELECT ContactID, CompanyName , tblInvoice.InvoiceDate, tblInvoice.Pamount FROM tblCustomer LEFT JOIN tblInvoice ON ContactID = tblInvoice.contact_id Contact ID CompanyName InvoiceDate InvoiceAmount 1 AppleBee Jan 1/2005 $33.44 1 AppleBee Jan 1/2005 $45.00 2 Staples 3 Office Depot Dec 12/ 2005 If you look at the above, applebee has two invoices on the same day, but we want the LAST invoice. So, we go: SELECT ContactID, CompanyName, tblInvoice.InvoiceDate, tblInvoice.InvoiceAmount FROM tblCustomer LEFT JOIN tblInvoice ON tblCustomer.ContactID = tblInvoice.contact_id WHERE ((tblInvoice.ID)=(select top 1 id from tblInvoice where contact_id = tblCustomer.contactID order by InvoiceDate desc, id desc))); You will note the "top 1" to grab the LAST inoivce, but if you don't have a autonumber, then that statement will return TWO records for the top 1. As for which direction to draw, and how to setup relationships? It is very important how you do this. I going to re-post a message of mine on this... A left join means that a query will return the "parent" records when the child table HAS NO correspond record. So, if we have Customers, and Invoices tables, a left join would give us: CustomerName InvoiceNumber AppleBee Donought Shop 1234 Doughnut Shop 1344 Note how AppleBee does NOT yet have a invoice number in the invoices table..but the query still returns the record. You have to use left joins for lookup values when you drop in many tables (can't use standard joins in this case). So, with a left join, the corresponding child record DOES NOT have to exist. Just think of "left" side can exist...but the right side does NOT have to ! A middle join, or so called inner join is the standard join, and BOTH tables have to have a value for the join. The above would produce: CustomerName InvoiceNumber Dounought Shop 1234 Doughutn Ship 1344 So, in the above inner join, our customer name of Applebee does not show, since that customer does NOT yet have a invoice record in the invoice table. To make a left join, you drop in the tables (in the query builder, or the relationship designer), and draw the join line to the appropriate filed between each table. You then double click on the join line. You then click on the join type button You get three options: Only include rows where the joined fields from both tables are equal (this standard default inner join) Include ALL records from "Customers" and only those records from "Invoices" where the joined fields are equal (this is our left join. So, our main table Customers will be returned in this query, REGARDLESS if the child records (invoices in this example) exist, or not!. This is left join Include ALL records from "Invoices" and only those records from "Customers" where the joined fields are equal This sis obviously a right join.... For forms, and sub-forms, and related tables, left joins are quite important. If you look at the following screen shot, you can see that most relations ships are this left join, and RI is enforced. http://www.members.shaw.ca/AlbertKal...Appendex2.html tblBgroup (booking group) for example may, or may not have payments made (tblPayments). Thus, you can add a booking group, and NOT have to add child records. However, full RI is enforced, and you can see the side ways 8 "omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for example is a simple lookup). It is GREAT that I can look at the ER diagram, and instantly know if child records are required, or they are not!! The tables that MUST have a child records can also clearly be seen. If you go from the tblBgroup to the its parent table, you will see table tblBooking. You can easily see that there is a 1 to many here also, but NO ARROW head exists. Thus, when I create a booking, my designs will ALWAYS ASSUME that a child records in tblBgroup (booking group) will exist (ie: I must code, and assume that when I add a tblBooking records, my code also assumes that a tblBGroup will also have to be added). In plain English this means that when I make a booking (reservation), my code assumes that you MUST have people in that booking. However, I most certainly allow people to be booked, but not yet have made any payments. So, your relationship(s) if done right should reflect the rules you as a developer want to maintain. I should point out that a left join, or a standard (inner join) both allow child records to NOT exist, but you still should correctly set this relationship, since when it comes to making reports, and writing code...I will know what my assumptions were at the time (ie: do I HAVE to add those child records for the software to function correctly. So, if I write code to make a booking, all of my code thus assumes that people are also to be added to the booking. Break that assuming of mine, and likely my code will break). So, the ER diagram can convey a lot about your designs. Down the road, I can now look at that diagram, and when writing code, I will know if the design can, and does assume if child records are required. If you look at that table, it is VERY RARE that I require the child record. That application has about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left join. Hence, you most certainly should set the relation in the window for future reference, and also it will help you when you create a query, or a report. Thus, if you allow a main reocrd to be added, and NOT have to have a child reocrd (one, or many), then you should set your relsonships to be a left join. About 80-95% of my relonaships are left joins...... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#12
|
|||
|
|||
Peter,
Peter Danes wrote: You're right, I just tried it. If I drag from the main table to the sub table, it works fine. If I drag from the sub table to the main table, it refuses to enforce RI. I would assume that this is simply because existing data does not comply with the RI requirement, i.e. you already have a record in the main table for which there is no corresponding record in the subtable. What determines the direction? Is it that one has an autonumber PK field and the other simply a long integer PK? No, this shouldn't matter. I just tried linking some other key fields to see what would happen and got 1-N, 1-1 and Undetermined for various combinations of fields, some key and some not. If you have a unique index on a field in one table, and relating it to another table via a field that is not unique, the relationship will be assumed to be one-to-many. If you are relating two tables via fields that are unique in both tables, as you are in your original examples (they are both primary key fields, and therefore automatically unique), the relationship will be assumed to be one-to-one. I also tried it on two autonumber PK fields and it refused to create the relationship at all. No, it doesn't make sense to have a relationship based on 2 Autonumber fields, since the value entered into the related table can not be determined by the value entered into the primary table. And the 1-1 line in the relationship window doesn't seem to give any indication of the controlling direction once the relationship is established. Is there some way to tell by looking, or do you have to dig into the tabledefs and know what attributes of the fields to look for? If you double-click on the join line to open the relationships properties, you will see the related fields listed in the top panel, and this will indicate which is regarded as the main table and which the related table. -- Steve Schapel, Microsoft Access MVP |
#13
|
|||
|
|||
Hello Albert,
sorry about the delayed response, I just got back home. Thank you for your thoughts on my problem. I'm certain that you're correct about the need for a primary key, but if you look again at my original post, I believe that I already have a perfectly functional one. Every record in the main table has exactly one corresponding record in exactly one of the subtables, and that one subrecord has as a primary key the autonumber generated as a primary key in the main table, copied from the main table when the subrecord is created. So each subtable contains either nothing or exactly -one- subrecord, which has the same ID for its primary key as the autonumber generated in the main table for the master record. Since this ID is unique to that one subrecord, I believe that it is a valid primary key, even though not generated as an autonumber in *that* table. The issue of multiple subrecords and needing the last one does not arise here. I know that requirements can change over time, but this is so specific to the design that if something arose where I needed multiple subrecords, it would pretty much mean a complete redesign anyway. If you still think I am wrong and need another autonumber field, I'd be pleased to hear more. Pete "Albert D.Kallal" píše v diskusním příspěvku ... Note that ms-access "knows" if it is a one to one, or a one to "many" based on the fact that the foreign key in the child table has a unique index setting (if you only allow ONE value of the same type...then it has to be a one to one. If you remove the duplicates allowed, then obviously you can have "many"). By the way, you are correct in that the child tables do NOT need a autonumber primary key. However, you REALLY REALLY REALLY REALLY shold put that autonumber field in. The reason for this is if later on you do want to relate a table to that child table, then you can without having to add that autonumber collum. Furhter, to get the last reocrd of a child table is VERY common question (last inoive, last order, last whatever!!). If you do NOT add a autonumber field, then grabbing the last invoice for a custom canbe difficlet. Adding a autonumber makes that reocrd UNIQUE, and even if you don't relatae data to that table, you should always as a rule have a UNIQUE way of working with a particlar reocrd. (this will bite you when you got code, or just about anything that needs to manipulate those child reocrds. So, a primary key is not only for reoanlships, but just for you to keep yourself sane, and allow yo to write code etc. that can work with a SINGLE reocrd in a table. So, don't bother to remove those autonubmer collums, as they will come to good use in the future. For example: If you have a autonumber, then to get the custoemrs last inoivce you can use: tblCustomer tblInvoice SELECT ContactID, CompanyName , tblInvoice.InvoiceDate, tblInvoice.Pamount FROM tblCustomer LEFT JOIN tblInvoice ON ContactID = tblInvoice.contact_id Contact ID CompanyName InvoiceDate InvoiceAmount 1 AppleBee Jan 1/2005 $33.44 1 AppleBee Jan 1/2005 $45.00 2 Staples 3 Office Depot Dec 12/ 2005 If you look at the above, applebee has two invoices on the same day, but we want the LAST invoice. So, we go: SELECT ContactID, CompanyName, tblInvoice.InvoiceDate, tblInvoice.InvoiceAmount FROM tblCustomer LEFT JOIN tblInvoice ON tblCustomer.ContactID = tblInvoice.contact_id WHERE ((tblInvoice.ID)=(select top 1 id from tblInvoice where contact_id = tblCustomer.contactID order by InvoiceDate desc, id desc))); You will note the "top 1" to grab the LAST inoivce, but if you don't have a autonumber, then that statement will return TWO records for the top 1. As for which direction to draw, and how to setup relationships? It is very important how you do this. I going to re-post a message of mine on this... A left join means that a query will return the "parent" records when the child table HAS NO correspond record. So, if we have Customers, and Invoices tables, a left join would give us: CustomerName InvoiceNumber AppleBee Donought Shop 1234 Doughnut Shop 1344 Note how AppleBee does NOT yet have a invoice number in the invoices table..but the query still returns the record. You have to use left joins for lookup values when you drop in many tables (can't use standard joins in this case). So, with a left join, the corresponding child record DOES NOT have to exist. Just think of "left" side can exist...but the right side does NOT have to ! A middle join, or so called inner join is the standard join, and BOTH tables have to have a value for the join. The above would produce: CustomerName InvoiceNumber Dounought Shop 1234 Doughutn Ship 1344 So, in the above inner join, our customer name of Applebee does not show, since that customer does NOT yet have a invoice record in the invoice table. To make a left join, you drop in the tables (in the query builder, or the relationship designer), and draw the join line to the appropriate filed between each table. You then double click on the join line. You then click on the join type button You get three options: Only include rows where the joined fields from both tables are equal (this standard default inner join) Include ALL records from "Customers" and only those records from "Invoices" where the joined fields are equal (this is our left join. So, our main table Customers will be returned in this query, REGARDLESS if the child records (invoices in this example) exist, or not!. This is left join Include ALL records from "Invoices" and only those records from "Customers" where the joined fields are equal This sis obviously a right join.... For forms, and sub-forms, and related tables, left joins are quite important. If you look at the following screen shot, you can see that most relations ships are this left join, and RI is enforced. http://www.members.shaw.ca/AlbertKal...Appendex2.html tblBgroup (booking group) for example may, or may not have payments made (tblPayments). Thus, you can add a booking group, and NOT have to add child records. However, full RI is enforced, and you can see the side ways 8 "omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for example is a simple lookup). It is GREAT that I can look at the ER diagram, and instantly know if child records are required, or they are not!! The tables that MUST have a child records can also clearly be seen. If you go from the tblBgroup to the its parent table, you will see table tblBooking. You can easily see that there is a 1 to many here also, but NO ARROW head exists. Thus, when I create a booking, my designs will ALWAYS ASSUME that a child records in tblBgroup (booking group) will exist (ie: I must code, and assume that when I add a tblBooking records, my code also assumes that a tblBGroup will also have to be added). In plain English this means that when I make a booking (reservation), my code assumes that you MUST have people in that booking. However, I most certainly allow people to be booked, but not yet have made any payments. So, your relationship(s) if done right should reflect the rules you as a developer want to maintain. I should point out that a left join, or a standard (inner join) both allow child records to NOT exist, but you still should correctly set this relationship, since when it comes to making reports, and writing code...I will know what my assumptions were at the time (ie: do I HAVE to add those child records for the software to function correctly. So, if I write code to make a booking, all of my code thus assumes that people are also to be added to the booking. Break that assuming of mine, and likely my code will break). So, the ER diagram can convey a lot about your designs. Down the road, I can now look at that diagram, and when writing code, I will know if the design can, and does assume if child records are required. If you look at that table, it is VERY RARE that I require the child record. That application has about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left join. Hence, you most certainly should set the relation in the window for future reference, and also it will help you when you create a query, or a report. Thus, if you allow a main reocrd to be added, and NOT have to have a child reocrd (one, or many), then you should set your relsonships to be a left join. About 80-95% of my relonaships are left joins...... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#14
|
|||
|
|||
Hello John,
sorry about the delayed response, I just got back home. I'd say it's just a bug, or an overlooked feature. The relationship window doesn't get reconstructed when the table structure changes, it seems. Whether the relationship actually changes or not is sort of irrelevant - if there is a unique Index on the foreign key, you can't add a second record anyhow; so regardless of what the relationship window shows, it's effectively one to one. I see. It would be nice if the window kept up with the table design, or at least had a "Renew" button. And if you add or delete fields in a table, the window does reflect those changes. But I guess it's a fairly minor point. A blank subform sounds suspiciously like one for which the Recordsource is not updateable. If you've changed the table structure and the relationships, you probably need to redefine each Subform's Recordsource to point to the (newly redesigned) tables; also check the Master/Child Link Fields. My guess is that the form was not updated to reflect the changed table structure. No, I don't think so. I read these groups a LOT, in fact, they are my primary source of information for troubleshooting, so you can probably guess how often I see your name and your posts. You're one of the last people I'd want to argue with, but this seems to be legitimate behavior. If you google the Access archives for 'blank subform', you'll find quite a number of references to this. And it worked that way even when I had the tables truly one-to-many. The gist seems to be this: when a subform's recordset contains NO records, AND the properties sheet has adding records disallowed, the entire subform is not displayed. The subform's container on the main form stays, but all the subform controls are invisible. It makes a certain amount of cockeyed sense, I suppose. If there are no records to show and you can't add any new ones, it's unlikely that you have much reason to work with the subform. But it also blocks access to any command buttons and such that you may want even if you have no records and no adds allowed. I have adds disallowed because I don't want someone to make a mess, as you pointed out could happen, by adding, for instance, both a book and journal subrecord for one master record. The easiest way I have thought of to do this is to not allow adds on the subforms. I create the proper subrecord in the proper subtable based on what letter the user enters for record type ("B" book, "M" magazine, "J" journal) with all fields blank except the primary key, requery to make the proper subform visible now that it has a record and allow the user to finish entering data in the proper subform. To Access, that is then updating an existing record, but to the user, it appears to be a seamless continuation of the data entry process. Pete "John Vinson" píse v diskusním príspevku ... On Tue, 6 Sep 2005 23:22:46 +0200, "Peter Danes" wrote: Hello John, thank you for the explanations. Additional comments inline... I understand that - my wording there was a little awkward. Much of my confusion came from Access leaving the lines in the relationship window as one-to-many even after I had altered the subtables. Is that an Access mistake? Does changing the tables and not changing the relationships leave the DB in some indeterminate 'between' state, or does Access change the relationship to function correctly and simply neglect to update the relationship window? Or does it leave them displayed that way deliberately for some reason? I'd say it's just a bug, or an overlooked feature. The relationship window doesn't get reconstructed when the table structure changes, it seems. Whether the relationship actually changes or not is sort of irrelevant - if there is a unique Index on the foreign key, you can't add a second record anyhow; so regardless of what the relationship window shows, it's effectively one to one. You presumably will have three subforms, one for Books, one for Magazines, and one for Journals. Exactly. One main form with three subforms and some other doodads which are not pertinent to this discussion. The subforms display nothing at all when there is no record, which I find a little aggressive. My preference would be to have the fields displayed but disabled; it seems more in line with standard Windows functionality, but it's not a big enough issue for me to waste time circumventing it. And at least the user will have no possible confusion with which subform to fill out. A blank subform sounds suspiciously like one for which the Recordsource is not updateable. If you've changed the table structure and the relationships, you probably need to redefine each Subform's Recordsource to point to the (newly redesigned) tables; also check the Master/Child Link Fields. My guess is that the form was not updated to reflect the changed table structure. To be squeaky clean, you may need some VBA code to ensure that you can only create *one* child record in one of the tables (i.e. you should not have any main table records which have both a Book and a Journal related record). Yes, I have that currently under construction and no problems. That sort of stuff is well within my capabilities. Excellent. John W. Vinson[MVP] |
#15
|
|||
|
|||
"Albert D.Kallal" wrote in
: I hate to disagree with a MVP, Albert, but may I chip in a little bit here? By the way, you are correct in that the child tables do NOT need a autonumber primary key. As I understand it, the set up is something like Publications (*PubsID, ArchiveNum, PhysLocation, etc) Books(*PubsID, Author, House, etc) FK (PubsID) references Publications Magazines(*PubsID, IssuesPerYear, etc) FK (PubsID) references Publications However, you REALLY REALLY REALLY REALLY shold put that autonumber field in. In other words, Books(*BookNum, PubsID, Author, House, etc) Unique (PubsID) FK (PubsID) references Publications Magazines(*MagazineNumber, PubsID, IssuesPerYear, etc) Unique (PubsID) FK (PubsID) references Publications The reason for this is if later on you do want to relate a table to that child table, then you can without having to add that autonumber collum. Surely, this is just the best reason for _not_ adding another ID column. Say there was a need for another table of Translations for local translations of magazines: you would presumably suggest a design like Translations(*MagazineNumber, *LanguageCode, etc) FK MagazineNumber references Magazines (leaving aside for a minute the question of having a PK *TranslationID...), while I would suggest something like this: Translations(*PubsID, *LanguageCode, etc) FK PubsID references Magazines (note that the relationship is constrained to the Magazines table, not the publications table) Now, say there is a need for a query of PhysicalLocations for each translation: the first variation would need a three-table join on Translations - Magazines - Publications, while the second would need only Translations and Publications. It's not an uncommon scenario, and in my view, relegates the Magazines table to an (unneccesary) mapping function. If you look at the above, applebee has two invoices on the same day, but we want the LAST invoice. So, we go: You don't really use autonumbers to provide a MostRecent function do you? I refer to the (I think) seventh commandment: Thou shalt not use Autonumber if the field is meant to have meaning for thy users. If you want to know the most recent invoice in one day, then you need a time field to sort by. No? Best wishes Tim F |
#16
|
|||
|
|||
You don't really use autonumbers to provide a MostRecent function do you? I refer to the (I think) seventh commandment: Thou shalt not use Autonumber if the field is meant to have meaning for thy users. If you want to know the most recent invoice in one day, then you need a time field to sort by. No? An excellent answer. And, yes, a timestamp is a good solution. However, one should point out that simply using a autonumber for order STILL does not give the actual numbers any meaning to the user. For example, we can state that we are going to use autonumbers for a relation, but that does not mean we spilled the beans, and told the user what we are using the autonumber for!!! In other words, breaking silence in that we are using a autonumber for something such as relationships does NOT break that above rule. So, we can tell a user that we are going to use autonumbers for relations, and that most certainly has a meaning to the user!!. So, the above concept being explain is that the user never sees, or assigns an actual meaning to a ACTUAL autonumber. So, using the autonumber for setting the order no more breaks the above rule then using them for relationships. As long as the user never writes down, or sees those autonubmers, .you are free to use them as you see fit (to build relationships, or to set order ---- and telling the user we are going to do this would not all of a sudden mean we can't use the auotnumber for a relationship!). To split hairs, the concept of "order" could be argued to have more meaning then the concept of a relationship. (the reason being that a autonumber can be a random number for relationships, but using the autonuber for order implies a increasing value all the time, so your debate is somewhat correct in that order does have meaning, but so does the concept of a relationship, but not as much meaning!!). As mentioned, a timestamp is a very good solution to retrieve the last record However, it still needs pointing out that another good rule in database designs is to ensure that ALL records have a unique identifier, or a primary key. This concept of having a PK is NOT ONLY for building relationships, but also to uniquely identify a record in a table. If we start discussion about databases, and codds rules, you will find that these scholars will quickly point out that a record in a table needs a Primary key to allow one to accomplish a normalized database. (but, lets not even go there..and again, a shrewd person could point out that a compound key consisting of the foreign key + the timestamp could produce a primary key). However, my point is that just because a child table don't seem to need a primary key, adding one gives you the ability to restive the last record. It gives you the ability to uniquely identify a record. And, for future designs, and modifications, a new child table can be added, and you will never have to worry if the parent table is missing a PK. So, adding a timestamp might solve some of these problems, but consideration needs to be given for other issues So, I think it is just a bonus to always have a PK. And, of couse, this is not a "rule", but simply a good suggeston on my part.... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#17
|
|||
|
|||
"Albert D.Kallal" wrote in
: If you want to know the most recent invoice in one day, then you need a time field to sort by. No? An excellent answer. And, yes, a timestamp is a good solution. However, one should point out that simply using a autonumber for order STILL does not give the actual numbers any meaning to the user. Fair point indeed: I'm still a bit unhappy about requiring autonumbers to be always in order... although they are called incrementing, there are too many situations when an AN will be created out of sequence... The point at which the number is allocated will be (a) significant and (b) probably provider-specific. For example: Joe is having a lot of trouble making up his mind about his new laser printer: the salesman has opened the invoice and is waiting to enter the HP LaserCannon 34009 with 5000 page sheet feed. Meanwhile, Joe's secretary nudges him and points to the empty cupboard, so he asks for some copier paper to be put on a new order (and a new invoice) for same day messenger deliver. Finally, he makes up his mind and goes for the BudgetBrick MagicWriter with the built in WAN and cofee-maker. Which invoice is the most recent? Local business rules may legitimately choose either one, but it certainly should not be an accidental side effect of SQL Server versus Jet architecture! However, my point is that just because a child table don't seem to need a primary key, adding one gives you the ability to restive the last record. It gives you the ability to uniquely identify a record. I would sooner stick needles in my eyes than recommend a table without a primary key: my point about the subclassing/ one-to-one relationship described was that the FK _is_ the PK (that should be the other way round) and there is no need to specify a new one. I am now way too old to get into arguments about substitute PKs in heirarchies of one-to-many relations; but this is a somewhat different thing. All the best Tim F |
#18
|
|||
|
|||
"Tim Ferguson" wrote in message
I would sooner stick needles in my eyes than recommend a table without a primary key: my point about the subclassing/ one-to-one relationship described was that the FK _is_ the PK (that should be the other way round) and there is no need to specify a new one. I am now way too old to get into arguments about substitute PKs in heirarchies of one-to-many relations; but this is a somewhat different thing. I'm with Tim on this one. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#19
|
|||
|
|||
"Dirk Goldgar" wrote in message
... "Tim Ferguson" wrote in message I would sooner stick needles in my eyes than recommend a table without a primary key: my point about the subclassing/ one-to-one relationship Ah, ok...if we are talking about a one to one relationship, then of course I agree!! (I kind of missed that point). To be fair, my response was NOT in the context of a one to one relationship, but a one to many. For a one to one, without question we already have a PK in the child table (that comes from the parent table), and thus one would NOT want a autonumber pk for the child table...... (this issue was so obvious, that I did not even think it was being debated !!!);. If I wrongly suggested that one should put a autonumber in a child table that is NOT the primary key, then my apologies, as I would not suggest that for one second. (and,in re-reading my post, I mentioned autonumber, but NOT pk - that is was not my intention!). Since the original question is in this context, then I am guilty of confusing here. However, I can assure you that my point is that you want a PK in the table...and if you already got one...then adding a autonumber to that table is just not a good idea at all! In reading this, we all actually seem to be on the same channel, but I was not 100% clear here. So, no, I would NOT suggest adding a autonmber field UNLESS it is the PK (and, in the original post....the child table already has a PK)...... I now see why you and Tim pointed this out, as it looked like I was suggesting to add a autonumber field that was NOT the PK..... So, great heads up here folks...and thanks!!! -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#20
|
|||
|
|||
"Albert D.Kallal" wrote in
: In reading this, we all actually seem to be on the same channel, but I was not 100% clear here. So, no, I would NOT suggest adding a autonmber field UNLESS it is the PK (and, in the original post....the child table already has a PK)...... That makes us all in violent agreement then! All the best Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Relationship feature/bug/accident | Peter Danes | General Discussion | 22 | September 11th, 2005 11:15 PM |
Relationship feature/bug/accident | Peter Danes | Using Forms | 22 | September 11th, 2005 11:15 PM |
Impossible? Relationship / Join Quandary | SteveTyco | Database Design | 1 | May 5th, 2005 01:58 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Re-establishing a broken relationship | David McKnight | Database Design | 2 | December 1st, 2004 10:49 AM |