Managing a many-to-one relationship from the 'one' end. (1 Viewer)

[wmphoto]

I've posted this in general as the answer could involve use of a query, or a report or it could involve changing the table structure, I don't know exactly which other forum would be most relevant.

Say I have a many to one relationship, for simplicity we'll call the table on the 'many' side tblThing and the table on the 'one' side tblGroupofThings.When managing the records in these tables it is important that each Group of Things contains at least one Thing, but it isn't important that each Thing is part of a Group of Things.

Things and Groups of Things are constantly being added to the table. As I see it, the only way to manage this is from the Things table, or at least a form based on the Things table, as I am editing a foreign key for tblGroupofThings inside tblThings. However it would be easier for me to manage it from the Groups of Things end, so that my workflow goes as follows:

I add a new Group of Things to tblGroupofThings,
I then 'put inside' that Group of Things, the Things that belong inside it (i.e. make the foreign key field in those Things point to the Group of Things.

Managing from the Things end means I have to start of with the new Groups of Things which are 'empty' at the moment, decide which things need to go in them then swtich to Things and remember which Things need to go into which Groups of Things.

I hope you can see what I'm getting at. I'm sorry for all the talk about putting records inside other records when I'm really talking about foreign keys. I know that's not know it really works but to the user that's how it should appear to work.

The only really viable option I can think of is a many-to-many relationship instead. I could certainly make it work, as far as the interface goes, but it seems wrong to me if a Things can conceptually be in more than one GroupofThings, when physically it can't, and may cause unforeseen problems down the line.

 

[gemma-the-husky]

put it another way

instead of things, let's say you have say DVD's, and you want to sort these by Film Category

so what you are saying is, that you are adding a DVD and you have no category in which to put it. So the thing to is to create the categories, and then put the films in the categories. now i suppose practically, you could have a temporary "unallocated"category in which to put them until you decide where they should go. that would work.

from a business point of view though, you ought to know what category the things need to go in to, at the time of adding them, surely?

 

[Beetle]

You'll probably need to manage this with a bit of code. You could put a FK field in Things to store the GroupID, but without RI enforced. Then set up two forms, one (like a single form) for adding Things - with or without Groups. Then a second Main form / sub form for adding Groups. Then use some event code in this form to check the Things table to make sure there is at least one Thing with this GroupID before closing the form or moving to the next Group record.

 

[Steve C]

Hi wmphoto,

You have two tables called tblThing and a tblGroup

If the following statement is true - A Group and have many Things in it But a Thing can only be in one Group

That’s a one to many relationship.

Another example of a one to many relationship might be one football team can have many players but a football player can only play for one team.

But. If records in tblThing can belong in many records in tblGroup and records in tblGroup can be in many records in tblThing – that’s a many to many relationship.

Another example of a many to many relationship is actors and films. An actor can be in many films and films have many actors.

So my question is. Is your database a “one team many players” or “many films many actors”?

 

[wmphoto]

Gemma-the-husky - it's sort of the opposite way to that. I have things and I have groups to put them in, but instead of 'I've added a thing, now what group does it belong in?' the process is 'I've added a group, now what things belong in it?'. So although being one-to-many means it's only really possible to manage from the 'thing' end, I really want to find a workaround of some sort to manage it from the 'group' end, as groups are what I'm adding.

Beetle - sounds good, although it might run into problems as groups get added in batches and only once several groups have been added do I go through group by group adding things to them... Sometimes I come to a group I have no things to put it and then I have to go back to things and create the things first.
If things were only ever created after the groups had been added then I could easily do this with a subform to add new things, the trouble arises in that often the things already existed before the groups were added.
It might be possible that I could put together some sort of code that would be in a form based on groups, allow me to select one or more things and then populate the FK field for those things with the GroupID. It doesn't necessarily need to prevent me from moving to the next group before I put things in it, it just needs to be obvious to me as I go through each new group that 'this group is still empty and must be filled before the data can be considered complete'. Any form based on Groups should fulfill this I think. A subform within said form would normally be perfect for such a task, except for the niggle in the above paragraph.

Steve C - It's one-to-many, I know that already, the topic title says precisely that. One workaround could be to set it up a many-to-many but use it as one-to-many, but as I say, there is potential for this to cause errors in future.

 

[Beetle]

Sounds like the business rules for this app are more like guidelines.

I would probably add a boolean (Yes/No) field to the Groups table to indicate if this Group is valid or not. Users would not be allowed access to this field, it would be managed by code.

The first time a Thing is added to the Things table with this GroupID I would use code to update the Valid field. If at any time the last Thing related to this Group is deleted, the code would update the Valid field to False ("invalid"). This would make it easy to separate "valid" Groups from "invalid" for updating, reporting etc.

 

[MarkK]

In a database you model a reality to achieve a goal, and how you construct that model depends on the reality and the goal. I don't see how you will produce a useful database system if the best description you have is "thing," the clearest organizing principle is "group of things." What are the specific things? What are their specific dimensions? What is the exact purpose? My assertion is that if you can't answer those questions with specificity, you can't write a useful relational database system.

 

[Steve C]

Sorry wmphoto, I entirely failed to read your post carefully enough.

I was confused by “Things” and “Groups of things”. My naming convention avoids all use of plurals because each record represents the only one of whatever it is. This helps me when thinking about a group or set of records, to remember I’m usually, in fact, thinking about a single Record in a Table above.

When I wrote my first answer, I hadn’t seen GTH or Beetle’s answer. They will be correct, follow their advice and you won’t go far wrong.

This thread title does indeed say “many-to-one” but I wondered if many-to-many might be more appropriate. So asked a (too) simple question; (Team/players or films/actors?) to test which might be right.

It seems to me, with the advantage of reading Lagbolt’s post, that, respectfully, there may be a problem with your schema, perhaps the relational paradigm might be flawed.

Perhaps this is a one-to-many-to-many-to-one relational scenario. I’m very interested to find out how this gets resolved.

Is it possible for you to say what the Things and GroupsOfThings really are? Or post a db? It’d be easier for me to get a grip on the problem. Most likely, it will have been encountered (and resolved) by someone on here already – then it’ll be very easy to sort out.

 

[wmphoto]

Yeah I can do, I had avoided it, because when I try to explain what tables are on the one and many side of the relationship it becomes less clear which is which as they both represent the same thing, but from different sources.

Things are transactions in my accounts
Groups of Things are transactions on my bank statement.

When I download a load of transactions from my online banking I need to reconcile each one with a transaction in my accounts. All transactions on my bank account must be reconciled with a transaction in my accounting system this will turn the transaction in my accounting system from Forecast to Actual and will instruct it to use the actual amount rather than the forecast amount I initially entered.

Transactions in my accounting system are for individual items, therefore can only be reconciled with a single transaction in my bank account, however, transactions in my bank account could have been paying for a number of things and would therefore be reconciled with a number of transactions in my accounting system.

 

[wmphoto]

I have an idea of what sort of form would be ideal, if it is possible.

It would be a form based on Statements (aka groupsofthings aka transactions on my bank statement), with a subform based on Transactions (aka things, aka transactions in my accounting system).

So far so, good, using the subform I can see if the selected statement has been reconciled with any transactions, and add new transactions which will automatically contain the key for the selected statement in the FK field.

The problem arises when rather than creating a new transaction (something I only do for unforecast expenses) I want to reconcile an existing transaction with the selected statement.

If possible I would like to have a list (combo box of some sort) of all the transactions which haven't yet been reconciled, which allows me to select one or more and then press a button to automatically populate the FK field in those transactions with the key for the selected statement... then refresh the subform.

 

[MarkK]

Things are transactions in my accounts
Groups of Things are transactions on my bank statement.

In this reality, your "things" have no relationship with your "group of things." The statement is issued by your bank. They cannot know anything about your transactions in your accounts. There must be some intermediate structure.

In addition, a statement is not a "thing" in respect to a database. A statement is how a reality gets communicated, but a transaction is not altered or defined in any way by the fact that it appears on this statement or that. Transactions matter, and if you called your bank about a mistake you would not give them a statement number, the object of your discussion would be a transaction, and you would report the date, the payee, the amount. You would not discuss the statement. "Statement" is a vessel with arbitrary limits.

Think about the problem now without the concept of a statement. Does that open any doors or simplify your thinking? And see how the specificity matters?

hth

 

[wmphoto]

The database is the intermediate structure.

The database records are not the statements themselves, they are transactions as they are listed on a statement, it is easier to just call them statements as there is already a table called transactions. The significance of a transaction listed on a statements is that is one which has been recorded by the bank and actually affects my bank balance, as opposed to transactions in the other table which are entered by me and don't affect my bank balance until the bank have actually included it in my statement.

The confusion is just caused by what I have chosen to call the tables, not what the records themselves actually represent.

Linking the two tables together is basically analogical to me sitting with my bank statement in one hand and a list of transactions I had expected to make in the other hand and checking that the two tally.

 

[Steve C]

I cannot do better than Lagbolt’s answer.

There is one only set of records (the real life transactions).

You’re trying to find out what’s real.

The tools you have are; your bank’s version of the transactions in Table1 and your own version of the same transactions set out in Table2.

There will be errors in both Tables, notwithstanding the (flawed) assumption is both Tables will not have the same error and therefore can safely be checked each against the other.

Table3 has a two Primary Keys being the Primary Keys from Table1 and Table2. Table3 represents the ratified version of the real life transactions.

There is a one-to-one relationship between all three Tables.

Don’t take my word for it though – I’m just guessing. There will be sample db for exactly your problem where it’s all been thought through.

Best wishes.

 

[wmphoto]

No there are two sets of records, actual transactions and forecast transactions (which are every bit as real as the actual ones).

At the moment I have them in a one-to-one relationship, effectively tagging the actual information from the bank onto the transactions I have already forecast (so that my cash-flow forecasts show the actual amount for actual transactions and the forecast amount for forecast transactions).

But because a single actual transaction can cover multiple forecast transactions I thought the job was best done as a many-to-one. My question was to find ways of managing a many-to-one relationship from the 'one' end (when the FK is obviously in the many table), not to delve deeper into what the tables actually represent and what the model should be.

This is why I originally just called them groups and things, I think once you have decided that something is a many-to-one relationship, what those records actually represent is irrelevant, the database doesn't know what it is, only that one is 'many' and the other is 'one'.

 

[Steve C]

You sound cross.

 

[wmphoto]

Not so much that, but it has been 8 posts now since my original question was addressed so I felt it was time to call it a day... I have an answer, it'll take a bit more work but I'm sure I know what I want to do now.

The table structure has worked well enough for the last two years, there's never once been a discrepancy between what my books say my bank balance is and what my bank says it is. I know it sounds like anathema to most people that the two tables seem to represent the same thing, but the reality of making the database workable is that the subtle differences between the two things would make a single table more trouble that it's worth.

A purchase isn't quite the same thing as a transaction on my bank account. Also as far as downloading statements from my bank is concerned, it is easier for me to copy and paste the transactions into a special table which has been designed for me to copy and paste into, and then link them to purchases. If I pasted directly into purchases I wouldn't be able to paste them all as a single block. I would have to paste each record individually alongside the record in purchases it belongs with.

Could it be that my naming of the tables is what has caused the confusion. if I'd called them purchases and transactions would it have made more sense?