CODEWITHSUNDEEP
androidsqldatabasesqliteandroid-sqlite
Mariusz Brona
Mariusz Brona

Reputation: 1609

SQLite values assigned between tables

I'm having a problem. How can I assign values between these two tables:

Id DeviceName 

1  Device1 
2  Device2 
3  Device3 
4  Device4 



Id Scenery

1  Scenery1
2  Scenery2
3  Scenery3
4  Scenery4

What I want to achieve is to have something like this:

Scenery1 have Device1, Device2, Device4
Scenery2 have Device2, Device3, Device4
Scenery3 have Device1, Device3, Device4

Sceneries and devices are added dynamically from UI. I thought about adding columns 'Scenery' dynamically with ALTER TABLE:

Id DeviceId Scenery1 Scenery2 ... SceneryX
1  1        
2  2

Is it good idea, or maybe make something else?

Upvotes: 0

Views: 50

Answers (1)

userM1433372
userM1433372

Reputation: 5507

How about adding an additional table:

Table: DeviceScenery
Id    DeviceId    SceneryId

The DeviceScenery.id field is not mandatory but useful for deleting a row based on a single key.

Scenery1 have Device1, Device2, Device4
would ook like:
Id    DeviceId    SceneryId
1     1           1
2     2           1
3     4           1

You need a Many to Many relation ship between your tables. In the relational database world ma y to many relationship are always solved by using a third table. See http://en.wikipedia.org/wiki/Many-to-many_(data_model)

Upvotes: 1

Related Questions