Can't figure out proper junction(?) table structure to connect 3 tables with different relationships (1 Viewer)

[Noruen]

Hi!

another year, another problem

I have three tables related to this issue:
- Transactions
- LogBook
- Events

Now:
- Each Transaction can belong to just one Event
- Each LogBook can belong to just one Event
- Each Event can have many Transactions and many LogBook records assigned
- Transaction and/or LogBook can belong to Event alone (ie. LogBook belongs to Event without any Transaction involved, or vice versa)

- (now the problem): Transaction can be assigned to just one LogBook record, but LogBook record can have many transactions assigned

To demonstrate this issue in natural language:

During Friday Party (Event) where i used car to go there (CarLog entry) i paid parking fee 3 times (so LogBook has 3 Transactions assigned).

Rules:
- if Transaction and LogBook are connected, they must belong to the SAME Event

The issue is: how to build junction tables/relationships so that I can keep Event assignment for Transaction-LogBook pairs consistent?

Now I have design like that each Transaction and LogBook are associated with Event separatedly in its own table and when there is need to connect them I do some magic with VBA to ensure Events are the same, but it:
- duplicates the data (two same Event IDs FK) in two tables)
- still makes it error prone as what Event is right when not the same?

Please, do you have any ideas how to solve it?

 

[plog]

I do not understand what a LogBook record is.

I understand Events and Transactions. How is a LogBook record different than a transaction? How similar are the fields of Events and LogBook?

Can you post either a blank database or bring all the tables into the Relationship Tool, expand so we can see all fields and then post a screenshot of that?

 

[Noruen]

I do not understand what a LogBook record is.

I understand Events and Transactions. How is a LogBook record different than a transaction? How similar are the fields of Events and LogBook?

Can you post either a blank database or bring all the tables into the Relationship Tool, expand so we can see all fields and then post a screenshot of that?

LogBook contains CarID, Odometer value, Destination and other attributes.

For example I can put Fuel costs as Transaction and link them with LogBook record. And in discussed cenario, to also link both Trans-LogBook to Event “Vacation” for example. So that is the difference.

I will try to put down some scheme when on PC

 

[Noruen]

Here is the scheme…

 

[cheekybuddha]

Remove LogBook_FK from tblTransactions (and the link to tblLogBook)

You know the LogBook for a transaction via the link through tblEvents

 

[Noruen]

Remove LogBook_FK from tblTransactions (and the link to tblLogBook)

You know the LogBook for a transaction via the link through tblEvents

That won't work as then I would not know to exactly what transaction LogBook record belogs to.

 

[cheekybuddha]

Why not?

When you make an entry in tblEvents you enter a transaction and a log book.

They are linked, no?

 

[Pat Hartman]

tblTransactions:
TransactionID
EventID_FK

tblLogBook
LogBookID
EventID_FK
TransactionID_FK

The problem with the logic is that you are using Log book to log two separate things and they each need different FK's. PLUS the two things are not related, otherwise the logbook would not need EventID_FK. You have created a circular relationship.

Just because you are logging two different things doesn't mean they should be logged to the same table.

 

[theDBguy]

Please pardon me for jumping in... Could you have any transactions entered without any relationship to a log entry, and only related to an event?

 

[Noruen]

@cheekybuddha they are, but consider you have many Transactions in Event but also many LogBook records in Event. How do I know which transaction belongs to which LogBook record?

@Pat Hartman TransactionID_FK in LogBook do not allow to assign many transactions to one LogBook record. So this is not a solution.

@theDBguy sure! Transaction and LogBook can be assigned to Event independently. So there max be Event with only LogBook records or only with Transactions.

Any ma problem is exactly how to handle case, when I jave Transaction related to LogBook


Maybe one more example why I need it:

I have Work Expense for business trip to Prague thats EVENT.

I havave made 4 transactions:
- Fuel on Pump
- Car Wash On the same Pump
- Hotel
- Restaurant

Now I traveled from Wien to Prague and Back. So I have 4 LogBook entries
- Wien
- Pump
- Prague
- Wien (back home)

Now, all record above from Transactions and LogBook belongs to the Event
But only LogBook “Pump” must also be connected with Transactions “Fuel” and “CarWash”.

without that connection it is trivial, but with that connection I have two times same Event related. So how to design this whole thing without duplitious Events…?

 

[plog]

I would force every Transaction to belong to a LogBook record and eliminate the direct link between Transactions and Events.

Events have LogBook records, LogBook records have Transactions, 1 to many to many

Here's where you say--'but some transactions don't have LogBook records, they go directly to an Event'. To which I reply--'they don't anymore'. If needed you add a default/catch-all LogBook record to an Event and then the Transaction gets entered to that LogBook record.

 

[Pat Hartman]

Let me clarify. Just because you own vehicles and pets and several homes doesn't mean that you should use the same log table to log events or transactions related to each of them.

When the entities are unrelated to each other, they cannot be logged in the same table because you have no "backward" pointer without adding multiple optional FKs to the Log. So, for this silly example, the log table would contain VehicleFK, PetFK, and HomeFK and two of the three would ALWAYS be null. MAKES NO SENSE. Just make a separate log table for each entity.

When the entities are related, you log based on the ID of the many-side or junction table.

 

[Noruen]

@plog interesting idea! However as some Events are simply not related to any LogBook, it means no Car involved… but it is worth a thought.

@Pat Hartman i agree! But can you more clearly describe how would you change/propose my design? It may be completely fucked up, thats why I'm here.

 

[Pat Hartman]

Without knowing what you will do with the log, I don't know what would be best. You could use separate tables so that you can enforce RI which is always my preference especially since you will almost certainly be looking at one data source when you use the log. You could add all the FKs to the log table and all their data fields which will be ugly. You could use an EAV (Entity-Attribute-Value) schema which makes a very long very narrow log. In an EAV, each row has only one logged field. For the EAV, you would also need a fourth column to define the entity.
EntityType, EntityFK, AttributeName, AttributeValue, plus an autonumber PK.

Transaction, 123, Fuel, 14.5
Transaction, 123, FillDate, 2/27/23
Transaction, 123, Driver, Joe
Transaction, 2286, CarWash, 2/27/23
Transaction, 2286, Cost, 38.50
Transaction, 89, Hotel, Hilton Hawaiian Village
Transaction, 89, 2/27/23

 

[Zidar]

Here is the scheme…

The scheme is Ok. Do this:
1) Create unique index on table Transaction on two columns as (EventId, TransId). TransId is unique so adding one more foeld yo the index keeps it uique (superkey is the name)
2)add foreign key to table Logbook.Side 1: tblTransactions (TransactionId,EventId), side many: tblLogBook(TransactionId,EventId). That will allow to insert Logbook records without any Transactions - leave tblLogbook.TransactionId as NULL. When you want to add a transaction to the Logbook you will carry over both (TransactionId,EventId). FK created ay the beggining will prevent missmatch between EventIds.
We can continue tomorrow, I am pn USA Eastern Time.

Here is the scheme