0

I am currently testing out something, and encountering an issue when trying to insert some values in a table.

I have 3 tables as following; Devices, Outdoor, indoor. 

**outdoor**             --->          **devices**             <----- **indoor**

net_id(pk)                    net_id(fk)               net_id(pk)

I wanted to have a relationship so that a device can be either outdoor or indoor, by having either the net_id of indoor or outdoor. Thus I did the following:

ALTER TABLE devices
ADD CONSTRAINT o_relationship
FOREIGN KEY (net_id)
REFERENCES outdoor (net_id);

ALTER TABLE devices
ADD CONSTRAINT i_relationship
FOREIGN KEY (net_id)
REFERENCES indoor (net_id);

Now my problem is, when i am trying to insert a value in devices, i am unable to do so unless the net_id values belongs to both indoor and outdoor table. This means that let's say "net_001" exists in indoor and outdoor table it allows me to insert it in devices, but if "net_001"exists in indoor and "net_002" exists in outdoor and let's say I want to add "net_001" or "net_002" in devices it won't allow me to do so. Thus I want to be able to do insertion in devices table that as explained above.

Hope i was clear enough to explain the issue. Thank you in advance!

comsci_dude
  • 194
  • 2
  • 13
  • You design is not good for this. Devices must be parent entity which contains all common attributes. Relation: Devices 1<->1 Outdoor (Same PK) and Devices 1<->1 Indoor (Same PK). Outdoor and Indoor table should contain all private attributes. – MinhD Apr 24 '14 at 09:49
  • I am not sure if the data structure for `indoor` and `outdoor` differ, but if they do not, then you should have only one table with an extra field added to it as `in_or_out boolean`. If `true` then `indoor`, else `outdoor`. – Ravinder Reddy Apr 24 '14 at 10:03
  • @MinhD Well, **devices** do not contain any common attributes with **Indoor** or **Outdoor** tables. Thus I was wondering if it is possible to have one column that can accept values of either **Indoor** or **Outdoor** tables. However based on the answers here I believe that doesn't look like it is possible in relational databases. – comsci_dude Apr 24 '14 at 10:06
  • @Ravinder I think that is the closest and fastest approach to do it aswell, as said earlier I was thinking if it is possible to do it in one column but I don't think it is. – comsci_dude Apr 24 '14 at 10:08
  • @user2457717: There is another way of referencing. You define a relation table `in_out_relation` table, in which map `indoor.net_id` to `outdoor.net_id` with a `in_out_relation_id` primary key. And use this `in_out_relation_id` in `devices` column as foreign key. – Ravinder Reddy Apr 24 '14 at 10:14
  • @Ravinder Yes, this solutions looks even better i think. This way I still maintain one column that references in or out net_id. thanks! – comsci_dude Apr 24 '14 at 10:22

4 Answers4

1

Use two columns in table device, one for indoor and one for outdoor, otherwise you can't distinguish which is indoor and which is outdoor because they may have same id as you describe.

ALTER TABLE devices
ADD CONSTRAINT o_relationship
FOREIGN KEY (net_indoor_id)
REFERENCES indoor (net_id);

ALTER TABLE devices
ADD CONSTRAINT o_relationship
FOREIGN KEY (net_outdoor_id)
REFERENCES outdoor (net_id);
linpingta
  • 2,324
  • 2
  • 18
  • 36
  • well I kinda knew that way, but i want to know if its possible to have one column and yet be able to insert values of either table. Thanks for trying to help me though. – comsci_dude Apr 24 '14 at 09:47
  • then try to use union: insert into device values select xx from in_door where condition1 union select xx from out_door where condition2. – linpingta Apr 24 '14 at 09:53
  • I think this is a right way, never heard that foreign key can be referenced from more than one table, nor from the view or using similar approach. – Elvedin Hamzagic Apr 24 '14 at 09:54
  • @lipingta I think I will have to do something that distinguishes them, possibly by adding an extra column that refers if it is indoor or outdoor. – comsci_dude Apr 24 '14 at 10:13
0

Of course according to what you're telling the engine, the ID must belong to both tables.

What you're trying to do is modelling inheritance on a relational database, which is widely studied and is a classical design question when it becomes to choosing the method. In your case, device is the parent class, and indoor and outdoor are child classes.

As mentioned here, you need to choose from one of the methods, as I quote from the referenced link:

Community
  • 1
  • 1
adrin
  • 4,511
  • 3
  • 34
  • 50
  • you are correct, when i am trying to do an insert, the id must belong to both tables. I thought it is possible to be done without any problem. However thank you for clearing this up for me :) – comsci_dude Apr 24 '14 at 10:10
0

But you can do different approach, making that devices contains primary key, and indoor and outdoor references it...

Elvedin Hamzagic
  • 825
  • 1
  • 9
  • 22
0

Maintain a relation table among devices and in & out devices.

Solution 1:

For Device_Relation Table

+------------------+--------------+
| Of Table         | Column       |
+------------------+--------------+
| Indoor           | net_id       | -- <--- pk
| Outdoor          | net_id       | -- <--- pk
| Devices          | device_id    | -- <--- pk
| Device_relations | d.device_id, | -- <--- pk
|                  | od.net_id,   | -- <--- fk
|                  | id.net_id    | -- <--- fk
+------------------+--------------+

Solution 2:

For In_Out_Relation Table

+------------------+--------------+
| Of Table         | Column       |
+------------------+--------------+
| Indoor           | net_id       | -- <--- fk
| Outdoor          | net_id       | -- <--- fk
| in_out_id        | in_out_id    | -- <--- pk
+------------------+--------------+

For Devices Table

+------------------+--------------+
| Of Table         | Column       |
+------------------+--------------+
| Devices          | device_id    | -- <--- pk
| In_Out_Relation  | io.in_out_id | -- <--- fk
+------------------+--------------+
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82