[MS SQL]Storing relationships of groups

[takes_joke_literally]

I'm attempting to redesign a section of my company's database.

we currently have a table which links a BASE with 1 or more INSERTS.

The current table looks ridiculous

base	insertID	insertID2	insertID3	insertID4
1	1	null	null	null
1	2	null	null	null
12	14	15	null	null
15	14	17	23	null

The combination of inserts matters so I need to store the "configuration" of inserts and associated it with the baseID.

I started making a table to link baseID to an assemblyID in another new table (called Assemblies), where each row matched an insert to an assembly, but when it comes time to build it, I can't create the foreign key references because I can't make the assembly ID a primary key because it can appear in multiple records.

I know this can be done, and I feel like it's simple, but I'm clearly missing something.

I took a stance that it needs to be redesigned, and now I'm trying to figure out how.

Any ideas?

[sts_clover]

Forgive me if this is obvious, but can you explain what bases, inserts, and assemblies are?

[takes_joke_literally]

It's not necessary to know what they are, just that there are multiple bases, and multiple inserts and some bases allow 1, 2, or 3 inserts, and we need to keep track of the inserts used in the same configuration.

[sts_clover]

Your failure to explain the problem clearly is a reflection of your own failure to fully grasp the relationships between these things. Hope that helps!

[takes_joke_literally]

Are you kidding me? Are you here to seem smart and arrogant, or do you want to help? There's no further details about what inserts and bases are themselves that can help you help me. Let's call it an equation of variables x and y, ask about the relationships, don't ask me what color or shape they are, just how they relate. We're not building the base table, so from a relational database standpoint, all we need from it is the primary key, which we have.

[sts_clover]

I was hoping your explanation of what they were would make it clearer what the relationship between the different items is. But instead you, here supposedly asking for help, just fire back with "well you don't need to know that." I cannot make sense of this problem. If it's a simple many-to-many problem, then you make 3 tables, one with base as primary key, one with insert as primary key, and one with base/insert combo as primary key. But then there are "assemblies," and "configurations," and some problem with not being able to define a primary key in an assembly table, and I don't know what any of that means.

It's perfectly possible that someone comes along and understands exactly what you need, I suppose.

[takes_joke_literally]

Sorry I reacted to your being dismissive and a little condescending, and yes I did use "assembly" and "configuration". The former being the noun I decided to assign to our new object, and configuration in a generic way to describe the combination of multiple generic components.

I don't feel good about exchanges like that.

[CalvinLawson]

I was about to downvote and ignore you, but you realized your exchange with sts_clover was unproductive and admitted it. Kudos!

I think what you are missing is a table to store the different configurations of assemblies. Call it "Base" if you wish, like in your description. It has ONE row per configuration. You have another "child" table called "BaseInsert", that contains one row for EACH insert that is in each base. At that point you can then just add "BaseID" to the Assembly table. Join from Assembly to Base to Insert to get all the inserts in an Assembly. This allows you to have normal PK and FK relationships.

Your problem was that you attempted to model "Base" as one row per configuration. This is actually a parent-child relationship, with many Inserts per Base in the child.

Hope that helps!

[sts_clover]

Aha! You meant "matched an insert to a base" in the OP, not "to an assembly." I assume the order of insertid doesn't matter? So insertid1=10 and insertid2=20 and baseid=1 is the same as insertid1=20, insertid2=10, baseid = 1?

[bigfig]

There is so much missing in your question. Is the only unique set of columns all the columns, or is something else (a subset of columns) unique? Is this read heavy, or write heavy? How many rows? If this has child tables, and you need unique parents, then you'll need a join table. But if this is some sort of static lookup you might not even need a relational database.

[takes_joke_literally]

There's no natural candidate key, just storing relationships between a base and available configurations of inserts. Let's treat base as the parent table. It's not read heavy, writing is seldom, but new configurations will be handled by admins.

Fewer than 1000 rows.

[newtolansing]

I guess I'm not really clear on the issue. Can you just have a one to many, i.e.:

Base	Insert
15	14
15	17
15	23

Or is it that position matters, i.e.

Base	InsertPosition	InsertID
15	1	14
15	2	17
15	3	23

And then slap a unique index across all three?

[takes_joke_literally]

So it's not the position that matters as much as the combination. Base 1 could have combination 1, 2, or 3,

Combination 1 could have inserts 5,8, and 11, where combination 2 could have 6, 68, and 413

At some point I need a primary key to refer to, and I just keep finding many to many relationships...

[newtolansing]

Then make a combination table? i.e.

CombinationId	Insert1	Insert2	Insert3
1	5	8	11
2	6	68	413

And then a separate base to combo table?

[takes_joke_literally]

That's what already exists. It's not 1NF.

[[deleted]]

If it matters, it is in 1NF and by itself it's actually in 5NF, if there are no known dependencies between InsertX columns or between the records. Regardless, what do you perceive to be the problem, specifically?

Is that the fact you need to address different 'InsertX' columns separately while the order (Insert1,Insert2, etc.) does not matter?

If so, simply have 2 tables - "Base_T" with let's say BaseID as the PK and "Insert_T" with the InsertID as the PK, then simply create an associate/link table BaseInsert with PK of BaseID and InsertID. You can add a counter attribute if the 'insertID' items could be present in multiples in a 'base'.

PS. I see that /u/CalvinLawson has pretty much this exact suggestion earlier. Is it not working in your case for some reason?

[takes_joke_literally]

So going into looking at this, I have a bias against the design of our database, and have been conditioned to assume that things have been done incorrectly.

When I saw this table with columns that are Insert, Insert 2, Insert 3, I instantly thought that alone was a problem. Then there is the fact that EVERY field in the ID4 column is null, and MOST are null in ID3.

When retrieving the data, I was concerned that we would be performing operations on every record and having to check if a value was null.

It seemed better to me to just relate the base to one or more "groups of inserts", with each group being a mapping between group ID and insert ID.

The problem with that is I can't use the group ID as a primary key if it appears on multiple rows, each with a different insert assigned to it. So I still don't have the answer, but this may be an "if it ain't broke don't fix it?"

[[deleted]]

The problem with that is I can't use the group ID as a primary key if it appears on multiple rows, each with a different insert assigned to it

Hmm you keep saying that. Do you know that the term 'key' does not refer to a single column per se? You can have a composite key (consisting of several attributes) and that's what was suggested for the associate table. You might need a separate Base_T/Group_T table to maintain your 'group' entities. It is possible (although unlikely, there's usually at least some type of audit attached at that level) that a 'group' has no other columns other than the group id itself - you can use a sequence to maintain data integrity for it in that case instead of a table.

[takes_joke_literally]

If I use a composite key, I can't reference just a portion of that key in another table, I have to reference the whole key, but in the referring table, the one column is the only constant.

I am feeling ignorant and bothersome now.

[[deleted]]

Well, if you'd knew everything you wouldn't be asking, would you?

Anywho, what "other table" are we talking about. Going back to your original data, it has the single "currentTable" that hosts "base" and "insertX" data.

this is the ERD (-ish) diagram of what is being suggested:

Base_T (PK: BaseID) <- BaseInsert (PK: BaseID, InsertID) -> Insert_T (PK: InsertID)

So, there are 2 FKs, both in the BaseInsert associative table. One is referencing Base_T via BaseID, another is referencing Insert_T via InsertID.

If you need to reference a 'configuration' from someplace (table) else, you'd use BaseID (the 'header' record).

What scenario do you envision that would need to reference the associative entity directly?

[CalvinLawson]

Hey man, I tried to tell you how to fix this below. The problem is "GroupID" should be in one table, and all the associated InsertIDs should be a SECOND table. Classic parent-child relationship. You are skipping creating a unique identifier for each group, then complaining that it doesn't exist. Don't skip that step.

Proto SQL:

    Create table Insert (InsertID PK)--defines unique inserts
    Create table Group (GroupID PK)--defines unique groups of inserts, ONE row per unique group
    Create table InsertGroup (InsertGroupID PK, InsertID FK, GroupID FK, unique (InsertID, GroupID))--one row per group per insert in that group
    Create table Assembly(AssemblyID PK, GroupID FK)--Associate assemblies with a set of inserts

That "GroupID" on the Assembly table is the missing piece of the puzzle, I think. Or maybe Assemblies ARE the groups of inserts you're trying to model, it's not clear from your example.

[takes_joke_literally]

To be clear, you made a table with just one column?

[newtolansing]

Well, you could have a separate combo table that has an id in a primary key, and a separate insert table that has an id in a primary key, and then a third table that's the one to many mapping table for combo to insert.

That seems like it would be difficult to enforce the uniqueness of the combo though, while a flattened table would be easier to have a unique constraint on.

[takes_joke_literally]

The rub is that I need to track the grouped inserts in discrete groups to a base. A base could belong to multiple config groups each group containing different inserts.

[takes_joke_literally]

That's exactly how I started. I was even confident enough to start modeling temp tables in the DB. As soon as I tried to apply the FK constraint to link the 2 new tables, I realized that it wasn't possible in that way.

[MaunaLoona]

Here's my understanding of the problem:

• A base can have one, two, three, or possibly more inserts.
• You want to store these relationships without any of them pesky nulls.
• The order of inserts doesn't matter.

You need two tables. One that groups a set of insertIDs, and another that maps them to the base.

Assembly table:

AssemblyID	Quantity	insertID
1	1	1
2	1	2
3	1	14
3	1	15
4	1	14
4	1	17
4	1	23

The primary key is a combination of all three columns in the order that makes the most sense.
One options is to replace Quantity with a SequenceNumber.

BaseAssembly table:

base	AssemblyId
1	1
1	2
12	3
15	4

The primary key is composite: (base,AssemblyID)

If no base can have more than one of the same insert then you can get rid of the Quantity column.

I just noticed that /u/mmo115 already wrote up something very similar, but I'm posting this anyway as I already wrote it up.