CODEWITHSUNDEEP
mysqllaravel-5eloquent
Snedden27
Snedden27

Reputation: 1930

Implementing many to many relation in mysql

I have two tables

1.Rectangle(rectId , xPos , yPos , height , width)

2.Scale(scaleId , someothercols...)

Now each column in the rectangle can have ' 0 to 1 ' scale associated with it.

i.e xPos can have a scale,yPos can have a scale and so on . So in general a single rectangle can have more than one scales.

Also all the above columns can also have no scale reference, in that case they would have a static value i.e xPos=50,yPos=60 so on..

Also a scale can be referenced by more than one rectangle .

What is the best way to implement this in sql

The way I thought of is having a junction table Rectangle_scale with rect_column attribute exg :

Rectangle_scale

| rectScaleId | rectId      | scaleId      | rect_col(string)|
|:----------- |------------:|:------------:|---------------- | 
| 1           |      1      |     2        |  Xpos           |
| 2           |      1      |     3        |  Ypos           | 
| 3           |      2      |     2        |  Height         |

Is this the right way to do this? Also do I need the rectScaleId col , or should I use rectId and scaleId as a composite primary key

Also, something to mentioned which is not directly related to the quesion, I am using Laravel 5.2 framework with eloquent, not necessarily required, but the solution which adheres to the limitation of Laravel's eloquent would be nice.

Upvotes: 4

Views: 84

Answers (2)

Cristofor
Cristofor

Reputation: 2097

Implementing Many to Many relations require a conjunction table as you say.

So according to your question the relation is fundamentally between rectange table and scale table.

Properties we need the tables to have:

  • every scale can be referenced as many as needed by any of the existent rectangles
  • every rectangle can be associated with more than one scale

This can be accomplished by creating the tables as follows:

create table rectangle (
  rectID int primary key auto_increment,
  xPos int,
  yPos int, 
  height int,
  width int)ENGINE = Innodb;

create table scale (
  scaleID int primary key auto_increment,
  descr text )ENGINE = Innodb;

create table rect_scale (
  scale int not null,
  rect int ,
  rect_col varchar(100),
  primary key (scale,rect),
  foreign key(scale) references scale(scaleID),
  foreign key(rect) references rectangle(rectID)     
)ENGINE = Innodb;

I've also created a demo on SQLFiddle: here

Upvotes: 1

borowis
borowis

Reputation: 1250

You don't need an intermediate table, your xPos and yPos could be foreign keys to the scale table like this:

CONSTRAINT xpos_scale_fk FOREIGN KEY(xpos) REFERENCES scale(scaleId) 
CONSTRAINT ypos_scale_fk FOREIGN KEY(ypos) REFERENCES scale(scaleId)

But you will need to create records in the scale table for that case:

Also all the above columns can also have no scale reference ,in that case they would have a static value i.e xPos=50,yPos=60 so on..

I don't completely understand what values you have in scale table but I would normalize your data so that xpos and ypos always have same kind of data -- references to scale. That way you could avoid more complicated setup of one more intermediate table.

So it would look like this (if I correctly understood your problem):

rectangle
rect_id, xpos, ypos, height, width
1, 0, 1, 2, 2

scale
scale_id, units, values
0, pixels, 50
1, pixels, 100
2, meters, 2

Then perhaps next step would be to decompose scale into units and values.

Upvotes: 0

Related Questions