Enforce Referential Integrity Limit

[Western_Neil]

I have a bunch (18+) of tables that all use FrChangeFK and ToChangeFK which is linked to tblChanges. The way I'm thinking is each Fr/To will give a full date (WorkDate). Now alot of things happen on the same day or close to, resulting from the same order (OrderFK), or by chance. Some may have a vague date (Early 1915) and different actions (Create, Split, Disband etc.).
MS Access limits Referential Integrity to 32 minus overhead (say 8) giving ~24. I was thinking a way around this would be to use a "Bypass" table (BypassID, ChangeID), which would be linked to both. I don't know if all should pass though these tables, using one or upto three tables, or use the table only on lesser used tables. For some I could also just hard code the date into the record (ie National Title Changes), but not enough of them.


Comments or better ways of doing this (Structure, Method, work arounds etc.)?


Neil


PS tblUnits is close to this limit as well
PPS I do know that this limit was discussed on a form and I think in the MS site. No work around expect for changing structure was mentioned.


tblChanges ChangeID OrderFK WorkDate ActionFK AccuracyFK ActualDate


tblUnitForces part of table includes FrChangeFK ToChangeFK
tblUnitServices part of table includes FrChangeFK ToChangeFK
tblUnitTiers part of table includes FrChangeFK ToChangeFK
tblUnitNations part of table includes FrChangeFK ToChangeFK
tblUnitSymbols part of table includes FrChangeFK ToChangeFK
tblUnitLocations part of table includes FrChangeFK ToChangeFK
tblUnitStaffs part of table includes FrChangeFK ToChangeFK


tblUnitHonours part of table includes FrChangeFK ToChangeFK
tblUnitHeritages part of table includes FrChangeFK ToChangeFK
tblUnitTBranchs part of table includes FrChangeFK ToChangeFK
tblUnitSizes part of table includes FrChangeFK ToChangeFK
tblUnitCommands part of table includes FrChangeFK ToChangeFK
tblBranchTitles part of table includes FrChangeFK ToChangeFK
tblUnitTitles part of table includes FrChangeFK ToChangeFK
tblForceTitles part of table includes FrChangeFK ToChangeFK
tblServiceTitles part of table includes FrChangeFK ToChangeFK
tblTierTitles part of table includes FrChangeFK ToChangeFK
tblNationTitles part of table includes FrChangeFK ToChangeFK

[ArviLaanemets]

Am I correct to assume, that:

You have a table for units.
And you have several tables for various unit characteristics (Forces, Services, Tiers, Nations, Symbols, Locations, Staffs, Honors, Heritages, Branches, Sizes, Commands).
And then you have some supertable tblChanges, where you determine, which value every of those statistics will have at which time interval!

You have got difficulties with this design currently, and more will be on way.

Register all changes in according characteristic history tables directly. Btw, no need for 2 dates at all - you need only the date, the new characteristic value will be valid at. And when at some moment certain statistic doesn't apply at all, you leave its value Null and enter only date. When you need to keep account, based on which order changes were made, add an orders registry (tblOrders: OrderID, OrderDate, ...), and in according characteristic history table enter OrderID as FK.

It looks like some tables you have listed here as characteristics, don't classify at all. E.c. tblUnitStaffs! When I'm not false entirely, this must be a personell registry for unit - i.e. nothing you can link with unit as a single value at any moment. At any time moment, an unit has at least several active members of staff. But from design point of view there is not any difference - you may have several rows in tblUnitStaffs to register a change, but you anyway can link every added row with some order.

[Western_Neil]

Am I correct to assume, that:

You have a table for units.
And you have several tables for various unit characteristics (Forces, Services, Tiers, Nations, Symbols, Locations, Staffs, Honors, Heritages, Branches, Sizes, Commands).

Yes. the Table for Units is a "Single Field Table", which I know could be a problem.

And then you have some supertable tblChanges, where you determine, which value every of those statistics will have at which time interval!

I not sure I call it a supertable, but I have gathered all the dates into 1 table. A big potion of the dates are the same and link together somehow.

You have got difficulties with this design currently, and more will be on way.

Thanks. You are full of good news.

Register all changes in according characteristic history tables directly. Btw, no need for 2 dates at all - you need only the date, the new characteristic value will be valid at. And when at some moment certain statistic doesn't apply at all, you leave its value Null and enter only date. When you need to keep account, based on which order changes were made, add an orders registry (tblOrders: OrderID, OrderDate, ...), and in according characteristic history table enter OrderID as FK.

What ?? I don't understand the first sentence. I have used two dates because the next (or previous) change is not documented. Are you suggesting a second record stating a period of unknown status? I do have a tblOrders: (FileNumber OrderName EffectiveDate IssuedDate OrderURL Authority). Orders can change a lot of characteristics (or just one), maybe missing in history, maybe done early or latter (or not even), but its the date thats important to me. Note the dates can be vague to specific, I'm dropping the time and assuming 0000hrs local for all changes.

It looks like some tables you have listed here as characteristics, don't classify at all. E.c. tblUnitStaffs! When I'm not false entirely, this must be a personell registry for unit - i.e. nothing you can link with unit as a single value at any moment. At any time moment, an unit has at least several active members of staff. But from design point of view there is not any difference - you may have several rows in tblUnitStaffs to register a change, but you anyway can link every added row with some order.

Again What?? Yes tblUnitStaffs will list the staff positions, the officers filling them and the date ranges. Yes it does tie back to change table (and therefore tblOrders) and Yes it takes several records to describe the units staff.

Each table has been created because I have found at least one occurrence of that value changing over time. My rough notes still list a lot of them in one table, and time can be decades.

It may look or sound like I know what I'm doing, but I have not designed a DB before or written Access code (VBA sure ..). I have seen good DB designs and bad (I think), and know that bad can lead to dirty ugly code, and clean is cool.

And thank you for taking the time to respond.

So the question is when asking MS Access to enforce referential integrity in a table for the N+ (24) time, it errors out. What is the fix for this?

[ArviLaanemets]

What ?? I don't understand the first sentence. ... Are you suggesting a second record stating a period of unknown status?

E.g. you have tblUnitLocations (the example covers both cases):

ULID, UnitID, ChangeFrom, Location, Comment
12      33     21.03.1912  London
44      33     01.02.1913  Null     The new location is unknown
91      33     13.08.1914  Essex

To get a parameter value at any given time moment you can use an UDF ValidValue() I posted recently in another thread.
https://www.accessforums.net/showthread.php?t=71974

So the question is when asking MS Access to enforce referential integrity in a table for the N+ (24) time, it errors out. What is the fix for this?

The easiest way is to create a design where the problem doesn't exist

When designing a database, don't create a design which feels good or cool for you at start, and then try to bend the database according your design afterwards. Instead go with flow! It will be easier for you, and as rule the result will work faster and is more user-friendly. Use strengths of program, and avoid it's weak points!

Btw, with design I adviced it will be easy to design user interface. E.g. you have a single form fUnits, where you select an existing unit or add new one, and edit permanent characteristics for it. On this form you have a tab control with pages for every changeable characteristic and for every subregistry like staff. On every page is a continuous subform for characteristic history, or for subregistry. Subforms are linked to Units form through UnitID, so whenever you add a new entry into subform, it will be autamatically linked with active unit in fUnits.

[Western_Neil]

Okay, thats I thought you were saying. Using your example (good one by the way), the middle line could be referencing an actual date of Early 1913 and the third line referencing a date of By 13.08.1914

The easiest way is to create a design where the problem doesn't exist

Right and that will be ... Off to discover new ways

Uhmm I agree, which is why I'm here.

Btw, with design I adviced ...

Hmm missed something. Are you suggesting to get rid of tblChanges ? That would shift the problem onto the number of connections tblOrders has. And maybe increase the complexity of changing guessed dates to firm.

Thanks so far

[ArviLaanemets]

Are you suggesting to get rid of tblChanges ?

Yes! It is easier to follow those changes in parameter history table!

That would shift the problem onto the number of connections tblOrders has.

You can have links and referential integrity at table level, but you must not! I myself prefer to define links when there is a need for this, p.e. when I define query and need somehow to use wizard. Mostly I write queries in SQL wiew not bothering with links at all. Excel creates them for queries anyway, when they arent defined at table level, but usually I havne't any interest in them. And instead referential integrity, you can achive same and more with form events. It takes more coding, but you have more control over process, and no limitations!

And maybe increase the complexity of changing guessed dates to firm.

??? How? In subform, the whole history is displayed in chronological order (it depends on your design how many rows you see in subform, but probably it is something between 10 - 30). and you can always design a report, which displays a list of changes with missing values for certain table or with certain values in some additional column of parameter history table, which indicates the reliability of change date (you set the default value of reliability to fully trusted for according form control - NB! for form control, not for table field - and when needed you correct it to less trusted).

[Western_Neil]

parameter history table

guessing at what this means, but no big deal (me thinks)

You can have links and referential integrity at table level, but you must not!

then why haae it? I agree it's more coding and control, why create more unless there is a downside.

??? How?

My original thoughts were along those lines. Then I found some orders changing things all over the place with back and future dates included.

So the question is when asking MS Access to enforce referential integrity in a table for the N+ (24) time, it errors out. What is the fix for this?

Your answer is Not to use it therefore no problem. Correct?

Going away this weekend (Sat/Sun), so I will think about this hard.

[Western_Neil]

So are you saying that I problablly will not be formally writing that many reports, but be working the data a lot by ad-hoc queries/reports?

[Western_Neil]

Since you suggesting to get rid of tblChanges,

tblChanges ChangeID OrderFK WorkDate ActionFK AccuracyFK ActualDate

those fields need to move somewhere else. Adding 5 fields to all those linked tables feels like reverse normalization, but it may make it better. In the super table I was worried about crossing data threads and getting a mess. This maybe cleaner but with duplication. Is this also what you where intending?