2

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.

cppcoder
  • 22,227
  • 6
  • 56
  • 81
  • This isn't one-to-many, but many-to-many (one config might have multiple services, and one service might belong to many configs, if I understood you correctly). Read: http://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de/7296873#7296873 – NullUserException Oct 10 '12 at 05:42

4 Answers4

4

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
.....
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • I will be querying this table based on `config_id`. So putting an index on this column alone would suffice? – cppcoder Oct 10 '12 at 05:56
2

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.

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • If you add `config_id` to `services` table, how once config can have multiple services? Different config can share the same service as well. – cppcoder Oct 10 '12 at 05:44
  • Why should I put a primary key on both columns? I want to query this table based on `config_id`. So, putting an index on `config_id` column would suffice? – cppcoder Oct 10 '12 at 05:54
  • PRIMARY KEY, not index. The primary key will ensure that you cannot insert the same entry twice. so you cannot insert the combination confid_id = 1 and service_id = 1 twice. – Adriaan Stander Oct 10 '12 at 05:56
  • If I put a primary key on the 2 column group, will the query based on `config_id` be faster? `SELECT service_id from configservicemap where config_id = 1` – cppcoder Oct 10 '12 at 05:58
  • Slightly, as it might use the key if you put it in order (config_id,service_id) as order does make a difference. you might still want to put an index on it too. Test to see what the difference in performance is. – Adriaan Stander Oct 10 '12 at 06:00
1

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.

No'am Newman
  • 6,395
  • 5
  • 38
  • 50
1

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

ManMohan Vyas
  • 4,004
  • 4
  • 27
  • 40
  • In this case, one `service_id` belong to one `config_id`. I want one `config_id` to have multiple `service_id`. Also 2 different `config_id` can share the same `service_id`. – cppcoder Oct 10 '12 at 05:48