Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 10-09-2008, 06:43 PM   #1
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,315
Thanks: 0
Thanked 90 Times in 72 Posts
Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all
Sharing a many side table with more than one one-side tables

Note to long-time forums regulars: This is basically a rephrasing of my old question in my neverending quest for normalizing conflicting models & business requirements. I'm posting mainly to get some simulation on my thoughts among the way and see how I can improve and seeing what others may have done in solving similar problem to this.



After examining things a bit more closely, I realized I actually had a many side table which I needed to be shared between two one side tables that has nothing to do with each other. The many side table is primarily a log table, while the two one-side table has nothing in common and are not related at all. Their relationship to the many-side table is primarily a mean for reference.

My solution was to set up the many side as thus:

ID (Primary Key)
OneSideTableA (Foreign Key to TableA)
OneSideTableB (Foreign Key to TableB)
.... (some fields for log stuff)

The relationship are defined for the respective table, guaranteeing that there won't be orphans, and I have a trigger in place to do a XOR test to ensure that I don't end up with both missing or both with some ID from either table. (This isn't really necessary as I use two separate forms for the respective updates/inserts, but I feel better having the extra constraint... just in case I do something stupid a year later, and get a hearty headsmack in return)

Seems pretty straightforward and easy to setup, though in further analysis, this actually violates the normalization on two different fronts.

First, I have a semi-repeating field (OneSideTableA & OneSideTableB) and this could conceivably be restructured thus:

ID
ParentTable (Identifies the parent table)
ParentID (stores foreign key)
....

But I no longer have a mean to establish a relationship.

Second, I suspect purists would argue that if I had two one-side tables sharing same many side table, the two one-side tables should be combined into a single table.

Indeed, that was my old solution. I defined a base table which would hold an entity and stored detailed information about an entity in a one-one table then related all many-side tables to that base table. (If you have no idea what the hell Banana is rambling on about, search the forum for three legged tables) After considerable analysis (thanks again, The_Doc_Man), that idea was showed to be defunct. Furthermore, there is no good reason to have a one-many side between the 'base' and 'detail'.

So, hence my current solution which sure looks fishy to me!

Will welcome any Deep Thoughts on this puzzle.

__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 10-09-2008, 07:17 PM   #2
georgedwilkinson
AWF VIP
 
Join Date: Mar 2008
Location: Houston, TX USA!!!
Posts: 3,856
Thanks: 0
Thanked 9 Times in 9 Posts
georgedwilkinson has a spectacular aura about georgedwilkinson has a spectacular aura about
Re: Sharing a many side table with more than one one-side tables

This is the standard "arc" configuration. In my mind, it addresses the same thing that inheritance and Interfaces in Java addresses...polymorphism and multiple inheritance.

There are multiple ways to implement polymorphism with tables like this. There are pros and cons to each. In my mind, the approach you take is all about how many different tables you have to build and maintain.

1. One table approach. This is only valid if the classes are truly subclasses. An example would be a Parties table with sub-classes of People and Organizations. There is a discriminator in the table (to discern whether this row is a Person or an Organization). All relationships are single FK type relationships with no arc. It doesn't sound like this will work for your situation.

2. Three table approach. This works in the instance you're talking about. You essentially outlined this approach. In the case of Parties, you would have a Parties table with an arc (either/or FK) from the Organizations table and the People table.

3. N + 2 table approach. Another valid approach but the number of arcs can get quite large if you need the same polymorphic object in multiple relationships. In this approach, there is no Parties table and every time you need to be able to use either an Organization or a Person in a relationship, you need to put a discriminator in the table with the arc. This could get out of hand quickly unless you only need the "Interface" once or maybe twice. This should work in your case.

I spent several vacations figuring this out using Oracle Designer, much to my wife's dismay. I hope it is helpful.
__________________
Thanks,
George Wilkinson
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
georgedwilkinson is offline   Reply With Quote
Old 10-09-2008, 07:30 PM   #3
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,315
Thanks: 0
Thanked 90 Times in 72 Posts
Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all
Re: Sharing a many side table with more than one one-side tables

George,

I'm so glad to know that I've re-invented the wheel. This tells me that I'm at least not wrong. And another thing- you made me realize that I was trying to shoehorn in OOP in a relational model, which explains the headache I have all time trying to make my tables 'reusable' and 'polymorphic'.

The #1 was basically my old solution. While I can make a case of those tables being subclasses, it was ultimately the conflicting requirements *and* needlessly complicated queries that killed the idea.

Can you provide some more information on 'arc configuration'? I briefly googled but didn't see to find any details on it as it pertains to database theory (or programming in general). (I ask mainly because I am not all sure if I understood the concept and/or if it's a new concept I've not heard of)

__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 10-09-2008, 07:56 PM   #4
georgedwilkinson
AWF VIP
 
Join Date: Mar 2008
Location: Houston, TX USA!!!
Posts: 3,856
Thanks: 0
Thanked 9 Times in 9 Posts
georgedwilkinson has a spectacular aura about georgedwilkinson has a spectacular aura about
Re: Sharing a many side table with more than one one-side tables

Quote:
Originally Posted by Banana View Post
George,

I'm so glad to know that I've re-invented the wheel. This tells me that I'm at least not wrong. And another thing- you made me realize that I was trying to shoehorn in OOP in a relational model, which explains the headache I have all time trying to make my tables 'reusable' and 'polymorphic'.

The #1 was basically my old solution. While I can make a case of those tables being subclasses, it was ultimately the conflicting requirements *and* needlessly complicated queries that killed the idea.

Can you provide some more information on 'arc configuration'? I briefly googled but didn't see to find any details on it as it pertains to database theory (or programming in general). (I ask mainly because I am not all sure if I understood the concept and/or if it's a new concept I've not heard of)
Like I said, it took me several years to understand all this stuff (though I only experimented on my time off). There is a nice writeup in the Oracle Designer 2000 help file, which I currently have open on my other (old) computer. If you'd like me to send what I can to you, PM me with your e-mail address. Not sure about the copyright information or I'd share it with the forum.

BTW, the Oracle help file shows a fourth solution which is so similar to one of the others that I didn't even bother with it.
__________________
Thanks,
George Wilkinson
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
georgedwilkinson is offline   Reply With Quote
Old 10-10-2008, 06:12 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,942
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Sharing a many side table with more than one one-side tables

Here is how I analyze it.

1. Requirement: Unified event logging with relational integrity for the individual events with respect to their relevant tables. (Project events, not Windows events, of course.)

2. Ease of maintenance: You have be able to reasonably manage the linkages into the many-side table. The more work you have to do, the worse off you are.

3. Ease of reporting: You want to be able to generate an event log. The more work you have to do, the worse off you are.

My take:

A. Always build separate event tables. Relational integrity becomes TRIVIAL. The space is almost the same. One extra TABLEDEF and its associated FIELDDEFS. Since it is a many-side table, it doesn't necessarily require a PK, just a good, non-unique FK. Which it could have with this scheme.

B. When you report on the unified data set, do so based on a UNION query of the tables where the UNION query supplies some constants for you.

SELECT "TABLEA" as TSource, TABLEA.REFID as TID, TABLEA.THISTEXT as TDESCR, TABLEA.WHEN as TWHEN FROM TABLE A
UNION
SELECT "TABLEB" as TSource, TABLEB.BREFT as TID, TABLEB.THATTEXT as TDESCR, TABLEB.DTM as TWHEN from TABLE B
etc etc

Then your reports can do order-by of TWHEN, you can select fields for group-by, you can data-mine by searching the query as though it were a table. It all would work off this query just as well as it would work with a unified table.

Well, not really - there is a performance issue with split tables. But as you point out, there is in your mind a normalization issue with your data set. I see it as really an issue in what strict normalization WON'T allow you to do. Let's look at those keys...

Your references to the external tables cannot be individual keys because for some portion of them, that value will be zero. The values in a record therefore sometimes do not depend on the individual FKs. They are NOT candidate keys for the event table when taken individually. OK, they were the many-side table, so that means they weren't going to be unique anyway. BUT some of them will now be zero/null/empty. Adding to your needs for filtration. Adding to the work to be done by Access.

To "really" normalize what you've got, you either need

1. a synthetic event sequence number and build a junction table for each individual source table showing which event links to which entry in that table... OR

2. you need to make the combination of referencing IDs a compound primary key.

Problem is, the latter approach breaks (and has to be fixed) every time you add a new source of event logs. Using the UNION query, one line has to be added to the query to bring the new event log into the fold. I'm lazy. I think of how much work it takes to go in and muck about with tables by adding a potential key.

Let's take a look at where the work is done.

To use the combined (single-table) approach, Access must evaluate a really nasty set of queries that boggle my mind totally. You are doing JOINs across the individual FK/PK for different tables. How will you NOT do a multi-layer or UNIONed query to bring together each JOIN (assuming this is desirable). This is like a multi-nested JOIN or a JOIN of at least three tables JOINed with an OR, not an AND, for each record. How do you MERGE the streams?

To use the UNION query approach, Access just merges individually simple record-streams and applies alias-names to corresponding fields. Then you use a query just like you would use a table. Seems to me that it is easier work to divide-and-conquer these event tables BUT then merge them only when you need them merged.

Just one man's view.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-10-2008, 10:04 AM   #6
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,315
Thanks: 0
Thanked 90 Times in 72 Posts
Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all
Re: Sharing a many side table with more than one one-side tables

The_Doc_Man,

Thanks for chiming in.

This looks simple on the surface, but suppose (no I don't have that particular problem... at least not yet anyway) that we had say, five one-side tables which all needed the same many-side table for logs/events/whatever purpose. If I'm reading your solution correctly, I would then create four tables which is replica of the original many-side table and just union them all?

Or would it be simpler to have a single many-side table with two fields, one to discriminate the originating one-side table and another to store the key from that one-side table?

I'm in solid agreement WRT wanting to set up a schema where there is minimum maintenance & work involved. In my case, I only have two one-side tables and (knock on wood) anticipate to not have any more table needing the same many-side table for the logs. Of course, I do not have the crystal ball and for all I know my government may decide there's one more thing to log next year. In such case, I may have to reconsider the schema but until I know the requirements I have, it's impossible.
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 10-10-2008, 08:21 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,942
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Sharing a many side table with more than one one-side tables

Quote:
If I'm reading your solution correctly, I would then create four tables which is replica of the original many-side table and just union them all?
Yes and no, depending on what you mean with "the original many side table." If you mean a table with a one-many partner that is the event log for that one table, then join the multiple event logs with a UNION query, yes. If you meant something else, don't know until you explain it.

Quote:
Or would it be simpler to have a single many-side table with two fields, one to discriminate the originating one-side table and another to store the key from that one-side table?
You again run into the "purity of key" problem. What is the key (even knowing it isn't going to be a prime key)? You need two fields - an FK that by itself cannot be used until you qualify it with a code for the discriminator field. Leaving you open to misunderstand which records match up. Further, unless you coordinate keys among the original PK side, you are going to duplicate some key numbers, which is a potential source of confusion.

Using the UNION query and supplying the table-code as a constant for the given leg of the query actually results in storage of less data. Another example of putting something in a query that really doesn't need to be stored in a table anyway.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-10-2008, 08:37 PM   #8
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,315
Thanks: 0
Thanked 90 Times in 72 Posts
Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all
Re: Sharing a many side table with more than one one-side tables

Quote:
Originally Posted by The_Doc_Man View Post
If you mean a table with a one-many partner that is the event log for that one table, then join the multiple event logs with a UNION query, yes.
That's what I meant. Going to absurd, would we still do the same for 100 tables needing their own many-side table for logs that are otherwise identical in structure?

Quote:
Using the UNION query and supplying the table-code as a constant for the given leg of the query actually results in storage of less data. Another example of putting something in a query that really doesn't need to be stored in a table anyway.
Mind qualifying this?

I'd think that the five clones of many-side tables would take more space because I have four redundant table definition for which they could be just one table (with an extra field to discriminate the keys' parent). I'm also assuming that data required for the many-side tables will remain the same whether we have five clone or single table with no explicit relationship defined, so the only difference would be the space consumed by definition and possibly separate indexes.... or am I missing something?
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 10-11-2008, 09:44 PM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,942
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Sharing a many side table with more than one one-side tables

Quote:
I'd think that the five clones of many-side tables would take more space because I have four redundant table definition for which they could be just one table
Yep, 5 x TableDef (fixed size) + 5 x FieldDef (fixed size) - Minus the space required for the discriminator field, which is multiplied by the number of records. OK, will you have, say, 1000 records? If the field is just an integer, that is 2k bytes right there. Will 2 Kb hold 5 tabledefs and 5 sets of fielddefs? I don't know. It might. Suppose the table grows to 4000 records and you still have that 2-byte index. OK, that's 8K for the discriminator column. Can you get the tabledefs and fielddefs in that much space? The initial overhead of my method isn't cheap but it doesn't grow.

Remember, the field doesn't exist at all if you use separate tables for each set of events. I.e. the field is implied by the table in which it resides. So storing a discriminator becomes redundant.

Quote:
Going to absurd, would we still do the same for 100 tables needing their own many-side table for logs that are otherwise identical in structure?
If you are going to maintain the linkage to the original tables, yes.

Here's where you have the issue: You want to link the event to a record in the master table. That is why this gets complex. If you just logged the event as a text-only event, the table could be a date/time stamp and a memo field. But where you come into grief is the need to keep the association. OK, if it is a requirement, so be it. But the overhead of the linkage is the price you pay.

Think of the complexity of doing this as a multi-way join. You now have 100 tables plus the message table to somehow nest. I don't think you could write that join.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 01-14-2019, 10:41 PM   #10
jack26
Newly Registered User
 
Join Date: Jan 2019
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
jack26 is on a distinguished road
Re: Sharing a many side table with more than one one-side tables

Hello,

I can't understand what are saying. Can you please explain in an easy way?? so that I'm giving you the right information about your query.
Thanks...
jack26 is offline   Reply With Quote
Old 01-15-2019, 05:11 AM   #11
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,599
Thanks: 26
Thanked 486 Times in 462 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Sharing a many side table with more than one one-side tables

Quote:
I can't understand what are saying. Can you please explain in an easy way?? so that I'm giving you the right information about your query.
Thanks..
I can in an example. Also it is a question of table design no a query question. This is a pretty common question, but this is the most complicated discussion. Suppose you want to store Notes and you want to store Notes for a lot of different things. Suppose you have Projects and Contracts. A project could have a lot of notes and a contract could have a lot of Notes. Lets say a note record in TblProjectNotes looks like
NoteDate, NoteShortName, NoteDescription, ProjectID_Fk
and one in TblContractNotes look like
NoteDate, NoteShortName, NoteDescription, ContractID_Fk

You may say to your self that looks weird. I have two tables basically holding the same entities (notes) with the same structure. Can I just make one table?
TblGenericNote
NoteDate, NoteShortName, NoteDescription, TableID_Fk, TableName

In this new table you have to tell the note which table it relates to

So the question becomes is the latter structure viable, and does it violate table Normalacy. You can make the generic table work, I have done it without problem. You can argue if it is proper. You cannot enforce referential integrity, however, you have to roll your own. The question is do you gain anything. In my mind it is a toss up. somethings are easier others are harder.
MajP is offline   Reply With Quote
Old 01-15-2019, 01:57 PM   #12
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,942
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Sharing a many side table with more than one one-side tables

Jack, please be aware that you inquired about an 11-year-old thread. I'm still here but I have not seen or heard from the original poster Banana in a long time. I don't recall his last post, but it was at least 7 or 8 years.

MajP's answer is pretty straight-forward. The issue is that sometimes, data purity issues can complicate a system where you really WANTED to maintain simplicity. Data purity is what enables relational integrity to work. Merging two tables that happen to look the same but that have radically different sources lets you use single-table maintenance methods, but you then can't establish relational integrity so easily because the sources might be independent and unrelated.

Where is your confusion and were you trying to offer a solution? OR were you trying to find a solution for a problem of your own?

If the former, Banana has moved on. If the latter, perhaps you could start a new thread. We don't charge for that.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 01-15-2019, 02:01 PM   #13
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,599
Thanks: 26
Thanked 486 Times in 462 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Sharing a many side table with more than one one-side tables

Here is the thread that I think asks the same question.
https://www.access-programmers.co.uk...ighlight=Notes

MajP is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Tables refering to more than one table jobro Tables 1 11-12-2007 05:06 AM
record ids auto number problem. jason2885 Forms 4 07-03-2007 10:16 PM
Copying data in one table to another-how to? jjh Tables 1 01-12-2007 11:25 PM
Another thing... Hellfire General 9 12-15-2006 02:16 PM
Joining 2 tables thegooner General 1 10-25-2006 02:41 AM




All times are GMT -8. The time now is 09:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World