CODEWITHSUNDEEP
sqlasp.net-mvcdatabaseentity-frameworkasp.net-core-mvc-2.1
George97
George97

Reputation: 23

Is there a better way to store data about 2 table relationship?

Currently, I have one SQL Database with a tables.

I'll call them table A, and table B. Each table A item is associated with anywhere from 0 to all existing table B columns (I use a sort of 'tags' system, which are stored in that B table)

Right now my solution to this was to create a third table, call it C, which simply has three columns -

Id(key) 
AId
BId
Count

example

So if I have A item with ID 1

Id| Name
1 | ColumnOne

Which is associated with 3 other items from table B - e.g, Id: 1, 2, 3

Id| Name
1 | TagOne
2 | TagTwo
3 | TagThree

Then C is going to look something like

Id|AId|BId
1 | 1 | 1
2 | 1 | 2
2 | 1 | 3

I just feel it's pretty redundant to have multiple entries for the same A table. Is there some better way to do this?

Upvotes: 1

Views: 409

Answers (1)

Dimitris Mavroforakis
Dimitris Mavroforakis

Reputation: 1

First of all, you have to determine the type of relationship between these 2 tables. If each record of table A is associated with multiple elements (0 or more) of table B, and each record of table B is also associated multiple elements of table A, then you have a many-to-many relationship. In this case, you have to create a third table (table C) with a composite primary key consisting of the primary keys of tables A and B. This means that you can drop the Id column:

Table C:

AId|BId
1 | 1
1 | 2
1 | 3

Upvotes: 0

Related Questions