CODEWITHSUNDEEP
sqlforeign-keysschemaentity-relationship
cppcoder
cppcoder

Reputation: 23095

Many to Many relation DB design

I have a table named config. Each config row has a list of services tied to it. There are a total of 10 specific services available.

How should I design the schema? The config schema already exists. This is my idea, but not sure if it is the right way.

config
===================
config_id primary key  
col-1  
col-2  
...  
col-n

I am planning to introduce a new table. 

serviceconfigmap 
======================================== 
config-id # foreign key to config table  
svc_id    # service identifier

The problem with this approach is that, the serviceconfigmap table will duplicate the svc_id column for each config-id

Is this the right approach? Any better idea is welcome

========================================

EDIT

I understand that I incorrectly termed my requirement as One-to-Many instead of Many-to-Many. Edited my question.

Each config can have multiple services and same servoce can be shared among different configs.

Upvotes: 2

Views: 516

Answers (4)

ManMohan Vyas
ManMohan Vyas

Reputation: 4062

Yes you are right , you will need third table to store foreign key of both tables.Hope that helps

Upvotes: 1

John Woo
John Woo

Reputation: 263683

You are designing for Many-To-Many relationship, since config has many services and services can contain on different config.

You need to have three tables for this, Config, Service and MappingTable

Config
=========
Config_ID         => unique
Config_Name
.....

Service
=========
Service_ID        => unique
Service_Name
.....

ConfigServiceMap
==================
Config_ID         
Service_ID
.....

Upvotes: 4

Adriaan Stander
Adriaan Stander

Reputation: 166336

If it is purely a 1->many I would only add a config_id to the table services

So your services tables would look something like

service_id (primary key)
config_id (foreign key)
col1
col2
...
coln

You only need a mapping table if it is many<->many

EDIT

Then this is not a one to many, but a many to many. You might want to stick to the many to many table you had, but put a primary key on columns (config_id, service_id) as to not duplicate the service per config entry.

Upvotes: 2

No&#39;am Newman
No&#39;am Newman

Reputation: 6477

The 'config' table as it stands currently contravenes first normal form in that it has repeating groups (one field for each service). What happens when a new service is defined? You will have to change the structure of the 'config' table. Thus a 'join table' between configurations and services is the standard way to go.

If one service can belong to several configurations, then a join table becomes a must.

Upvotes: 1

Related Questions