Are there quicker methods for a many-to-many relationship without a third table

[david8mary]

I have a database with two tables, Tubing, and connectors. Some tubes use multiple connectors resulting in a many to many relationship.

I have reviewed multiple videos and forums online detailing the process of creating this relationship and they all involve using a third associative table. This information comes from sources using older versions. I have the most up-to-date version of Access.

This method works pretty well. However, it becomes rather time consuming as I have 100+ tubing parts. Creating this third table essentially doubles my data input.


So my questions regard methods that exist or were introduced to newer versions of access that my solve my issue.

1. Can I place something like a delimiter between foreign keys such as "2,3,6" from the connectors
2. If the answer to 1 is no, are there methods in creating the associative table so that there can be a variable number of connectors per tubing. It is to my understanding that you would need multiple tables setup for something like 1 tubing for 5 different connectors.
3. If none of questions are fulfilled, are there alternative data management software that would be worth looking into?

[Seth Schrock]

The idea of a join table (the third table) has nothing to do with database capabilities, but with the theory of database normalization. If you have a situation where one tube can have many connectors and one connector can fit many tubes, then yes, you need three tables to store the tubes, connectors, and their association with each other.

However, with proper front-end development, you shouldn't have to duplicate your data entry. Or more accurately, you can let the computer add the records needed. For example, you can have a form for your tubes that has a subform based on the join table. The Connector ID field would be shown using a combo box that lets you select existing connectors. If the connector you want doesn't exist, then there are many ways to add it and then have it selected automatically. Thus one piece of data entry by the user results in a new record in the connectors table as well as the associated link in the join table.

One thing to remember is that the way that a computer stores information in the most usable, non-redundant way (normalized) doesn't make sense to the user. The way data is viewed by the user is a terrible data storage method. The job of the developer is to transform the way the data is stored and present it to the user in a user-friendly way.

[NeoPa]

It actually sounds like you may have a big misunderstandin g built into this question D&M. If my understanding is correct, and you have multiple possible connectors per tube, but only one possible tube per connector, then what you have is a One-to-Many (OM) relationship, rather than a Many-to-Many (MM) one.

Actually, Seth's explanation is pretty solid for both scenarios. You can, quite straightforward ly, design an interface in Access (Version agnostic) that does all the repetitions for you and ensures everything matches properly under the hood.

However, if it's an OM rather than an MM, you can get away with a standard OM link which involves PKs in every record along with a single FK in the Many-side table. Much more straightforward and easy to work with.

Just to illustrate what a MM relationship is, consider cars and people within households. When I were a lad, this may have been considered a One-to-Many in that the family car would be driven by both parents - possibly. Nowadays there may be more cars per household and some cars driven by multiple members of the family. It would not be adequate to specify who drove each car, nor which car was driven by each family member. Each connection would need to be specified separately. That's where the JOIN tables come in - but only when the potential links are that complicated.

[NeoPa]

David&Mary:
Can I place something like a delimiter between foreign keys such as "2,3,6" from the connectors

Technically this is possible, but do be aware that taking that approach severely limits what a database can do for you.
My less technical answer would be "Don't go there! Don't even think about it!".

David&Mary:
If the answer to 1 is no, are there methods in creating the associative table so that there can be a variable number of connectors per tubing. It is to my understanding that you would need multiple tables setup for something like 1 tubing for 5 different connectors.

I don't believe that's right. Refer to my earlier post, but if this is a simple OM connector then all you need is an FK in your Connector table. The Tubing doesn't point to all the Connectors. All the Connectors point to the Tubing.
I strongly recommend that you familiarise yourself with Database Normalisation and Table Structures.

David&Mary:
If none of questions are fulfilled, are there alternative data management software that would be worth looking into?

I suspect that your current issues are not really issues at all, but the simple result of your not, yet, being fully aware of what a database can do for you, and how. This is very understandable even for experienced programmers. Database work takes a whole new mental approach that many struggle with before their Eureka moment.

If you still feel you have a problem when you've followed the advice already given then perhaps we need to look into it again with you.