Implementing a 1 to many relationship with SQLite

Question

I have the following schema implemented successfully in my application. The application connects desk unit channels to IO unit channels. The DeskUnits and IOUnits tables are basically just a list of desk/IO units and the number of channels on each. For example a desk could be 4 or 12 channel.

CREATE TABLE DeskUnits (Name TEXT, NumChannels NUMERIC);
CREATE TABLE IOUnits (Name TEXT, NumChannels NUMERIC);

CREATE TABLE RoutingTable (DeskUnitName TEXT, DeskUnitChannel NUMERIC, IOUnitName TEXT, IOUnitChannel NUMERIC);

The RoutingTable 'table' then connects each DeskUnit channel to an IOUnit channel. For example the DeskUnit called "Desk1" channel 1 may route to IOunit name "IOUnit1" channel 2, etc.

So far I hope this is pretty straightforward and understandable. The problem is, however, this is a strictly 1 to 1 relationship. Any DeskUnit channel can route to only 1 IOUnit channel.

Now, I need to implement a 1 to many relationship. Where any DeskUnit channel can connect to multiple IOUnit channels. I realise I may have to rearrange the tables completely, but I am not sure the best way to go about this.

I am fairly new to SQLite and databases in general so any help would be appreciated.

Thanks

Patrick

Answer 1

Actually, you already have 1 to many relationship because the fields are not keys and/or are not declared as unique.

So you can add multiple records in the RoutingTable with the same DeskUnitName and DeskUnitChannel values.

Answer 2

Your RoutingTable is a typical implentation of a many-to-many relation. For example:

DeskUnitName  DeskUnitChannel  IOUnitName  IOUnitChannel
A             1                 A           1
A             2                 A           1
B             1                 A           1
B             1                 A           2

This would connect three DeskUnitChannels to one IOUnitChannel (A1), and two IOUnitChannels to one DeskUnits (B1).

A more normalized solution would look like:

DeskUnit         DeskUnitId, Name, NumChannels
DeskUnitChannel  DeskUnitChannelId, DeskUnitId, Channel
IOUnit           IoUnitId, Name, NumChannels
IOUnitChannel    IoUnitChannelId, IoUnitId, Channel, DeskUnitChannelId

This approach has no many-to-many relations. But it has three one-to-many relations:

• a desk unit has zero or more desk unit channels
• an IO unit has zero or more IO unit channels
• a desk unit channel can have zero or more IO unit channels

Many-to-many relations tend to add a lot of complexity to an application. I try to avoid them unless they are really necessary.