One-to-One Table keys
in [Tables]
|
From: Jerry Whittle on 5 Feb 2010 15:06 You would be better off storing it all in Table2 then. Access doesn't waste space. If you have an empty text field in a record, no space is used. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "oldblindpew" wrote: > The purpose of Table3 is to hold information applicable to only SOME of the > records in Table2. I didn't say there would be a record in 3 for every > record in 2. The whole point of having a separate Table3 is to have matching > records only as needed, to avoid storing lots of empty values. > --Pew > > "Jerry Whittle" wrote: > > > If Table3 is only to hold a single record for each record in Table2 and its > > only purpose is hold data that only applies to a subset of the records in > > Table2, I would much rather keep all the data in Table2 and leave the > > unneeded fields null. > > -- > > Jerry Whittle, Microsoft Access MVP > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > > > > "oldblindpew" wrote: > > > > > Table1 is the parent table, Table2 is the child table. Table3 is joined to > > > Table2 in a one-to-one relation. The purpose of Table3 is for additional > > > fields that only apply to a subset of the records in Table2. > > > > > > A form/subform is used to view Table1 header info with Table2 detail info. > > > I think I want a similar form/subform to view Table1/Table3. > > > > > > Q: Does this mean the same key field used to relate Tables 1 & 2 must also > > > be present in Table3, making Table3 another child to Table1? > > > > > > It seems this must be so; on the other hand it might be unnecessary in view > > > of the one-to-one relationship. The records in 3 are like extensions of the > > > records in 2, and could appear to somehow inherit the relationship back to > > > the parent table. > > > > > > Thanks, > > > OldBlindPew
From: oldblindpew on 5 Feb 2010 18:06 So... Is Jeff saying yes, it makes sense to have Table3, while Jerry is saying no, put Table3 fields inside Table2? Pew "Jeff Boyce" wrote: > That makes sense. > > If you create a query that joins Table2 and Table3, and make it > "directional" (all the records from T2 and ANY that match in T3), you could > use the query as a source for a form. > > Regards > > Jeff Boyce > Microsoft Access MVP > > -- > Disclaimer: This author may have received products and services mentioned > in this post. Mention and/or description of a product or service herein > does not constitute endorsement thereof. > > Any code or pseudocode included in this post is offered "as is", with no > guarantee as to suitability. > > You can thank the FTC of the USA for making this disclaimer > possible/necessary. > > "oldblindpew" <oldblindpew(a)discussions.microsoft.com> wrote in message > news:E1C253EA-405F-49C3-8E0B-FDEBE21B6602(a)microsoft.com... > > The purpose of Table3 is to hold information applicable to only SOME of > > the > > records in Table2. I didn't say there would be a record in 3 for every > > record in 2. The whole point of having a separate Table3 is to have > > matching > > records only as needed, to avoid storing lots of empty values. > > --Pew > > > > "Jerry Whittle" wrote: > > > >> If Table3 is only to hold a single record for each record in Table2 and > >> its > >> only purpose is hold data that only applies to a subset of the records in > >> Table2, I would much rather keep all the data in Table2 and leave the > >> unneeded fields null. > >> -- > >> Jerry Whittle, Microsoft Access MVP > >> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > >> > >> "oldblindpew" wrote: > >> > >> > Table1 is the parent table, Table2 is the child table. Table3 is > >> > joined to > >> > Table2 in a one-to-one relation. The purpose of Table3 is for > >> > additional > >> > fields that only apply to a subset of the records in Table2. > >> > > >> > A form/subform is used to view Table1 header info with Table2 detail > >> > info. > >> > I think I want a similar form/subform to view Table1/Table3. > >> > > >> > Q: Does this mean the same key field used to relate Tables 1 & 2 must > >> > also > >> > be present in Table3, making Table3 another child to Table1? > >> > > >> > It seems this must be so; on the other hand it might be unnecessary in > >> > view > >> > of the one-to-one relationship. The records in 3 are like extensions > >> > of the > >> > records in 2, and could appear to somehow inherit the relationship back > >> > to > >> > the parent table. > >> > > >> > Thanks, > >> > OldBlindPew > > > . >
From: Jeff Boyce on 5 Feb 2010 19:26 Nope, I'm telling how you could accomplish what you said you wanted to with the table structure you gave us. If you want to modify your table structure per Jerry's suggestions, you'd need to modify your query. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "oldblindpew" <oldblindpew(a)discussions.microsoft.com> wrote in message news:B4F3E12E-A261-466B-980C-544F6BE08962(a)microsoft.com... > So... Is Jeff saying yes, it makes sense to have Table3, while Jerry is > saying no, put Table3 fields inside Table2? > > Pew > > "Jeff Boyce" wrote: > >> That makes sense. >> >> If you create a query that joins Table2 and Table3, and make it >> "directional" (all the records from T2 and ANY that match in T3), you >> could >> use the query as a source for a form. >> >> Regards >> >> Jeff Boyce >> Microsoft Access MVP >> >> -- >> Disclaimer: This author may have received products and services mentioned >> in this post. Mention and/or description of a product or service herein >> does not constitute endorsement thereof. >> >> Any code or pseudocode included in this post is offered "as is", with no >> guarantee as to suitability. >> >> You can thank the FTC of the USA for making this disclaimer >> possible/necessary. >> >> "oldblindpew" <oldblindpew(a)discussions.microsoft.com> wrote in message >> news:E1C253EA-405F-49C3-8E0B-FDEBE21B6602(a)microsoft.com... >> > The purpose of Table3 is to hold information applicable to only SOME of >> > the >> > records in Table2. I didn't say there would be a record in 3 for every >> > record in 2. The whole point of having a separate Table3 is to have >> > matching >> > records only as needed, to avoid storing lots of empty values. >> > --Pew >> > >> > "Jerry Whittle" wrote: >> > >> >> If Table3 is only to hold a single record for each record in Table2 >> >> and >> >> its >> >> only purpose is hold data that only applies to a subset of the records >> >> in >> >> Table2, I would much rather keep all the data in Table2 and leave the >> >> unneeded fields null. >> >> -- >> >> Jerry Whittle, Microsoft Access MVP >> >> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. >> >> >> >> "oldblindpew" wrote: >> >> >> >> > Table1 is the parent table, Table2 is the child table. Table3 is >> >> > joined to >> >> > Table2 in a one-to-one relation. The purpose of Table3 is for >> >> > additional >> >> > fields that only apply to a subset of the records in Table2. >> >> > >> >> > A form/subform is used to view Table1 header info with Table2 detail >> >> > info. >> >> > I think I want a similar form/subform to view Table1/Table3. >> >> > >> >> > Q: Does this mean the same key field used to relate Tables 1 & 2 >> >> > must >> >> > also >> >> > be present in Table3, making Table3 another child to Table1? >> >> > >> >> > It seems this must be so; on the other hand it might be unnecessary >> >> > in >> >> > view >> >> > of the one-to-one relationship. The records in 3 are like >> >> > extensions >> >> > of the >> >> > records in 2, and could appear to somehow inherit the relationship >> >> > back >> >> > to >> >> > the parent table. >> >> > >> >> > Thanks, >> >> > OldBlindPew >> >> >> . >>
From: John W. Vinson on 6 Feb 2010 01:49 On Fri, 5 Feb 2010 15:06:04 -0800, oldblindpew <oldblindpew(a)discussions.microsoft.com> wrote: >So... Is Jeff saying yes, it makes sense to have Table3, while Jerry is >saying no, put Table3 fields inside Table2? This is at least a 25-year old argument about how to handle Subclassing. It can be done either way - with one table with a lot of NULL fields, or with two tables in a directional one-to-one relationship. Either one works. Both have advantages, and both have disadvantages - the one-table approach, as you rightly say, has a lot of NULL fields. Fortunately in Access' implementation they don't take up any space in your database, but they do make the table less elegant. The two-table approach makes your queries more complicated whenever you need to include the subclass table; if it's rarely needed, this may not be a big deal. It can also impact performace since Access must pull in both tables and the indexes on the primary and foreign key fields. Take your pick! -- John W. Vinson [MVP]
From: oldblindpew on 8 Feb 2010 11:37
Thanks, John for your plainspoken reply. It is inevitable, I suppose, that having so many ways to skin the cat complicates the instruction manual. Based on the doctrine that Records are Cheap but Fields are Expensive, I had surmised that having more tables was preferable for both programming effort and performance, despite all the complications necessary to relate them. Now it sounds like the opposite is true, at least in this particular case. Am I correct in saying that the answer to my original question is that there is no need to repeat in Table 3 the foreign keys from Table2, since I will be joining these tables anyway by means of a query? Also, this discussion prompts me to ask, is it better in general to specify the whole record as a RowSource, even though I don't need all the fields, or should one specify only the fields needed? In other words, is it more trouble for Access to pick out the specific fields than it is just to grab the whole table? Thanks Pew "John W. Vinson" wrote: > On Fri, 5 Feb 2010 15:06:04 -0800, oldblindpew > <oldblindpew(a)discussions.microsoft.com> wrote: > > >So... Is Jeff saying yes, it makes sense to have Table3, while Jerry is > >saying no, put Table3 fields inside Table2? > > This is at least a 25-year old argument about how to handle Subclassing. It > can be done either way - with one table with a lot of NULL fields, or with two > tables in a directional one-to-one relationship. > > Either one works. > > Both have advantages, and both have disadvantages - the one-table approach, as > you rightly say, has a lot of NULL fields. Fortunately in Access' > implementation they don't take up any space in your database, but they do make > the table less elegant. > > The two-table approach makes your queries more complicated whenever you need > to include the subclass table; if it's rarely needed, this may not be a big > deal. It can also impact performace since Access must pull in both tables and > the indexes on the primary and foreign key fields. > > Take your pick! > -- > > John W. Vinson [MVP] > . > |