How to store word compositions in a relational database in a normalized way?

Question

I'm trying to find a nice way to store word compositions of the following form:

exhaustcleaningsystem
exhaust cleaning system
exhaustcleaning system
exhaust cleaningsystem

The combinations are given by a default per case. Every word in a composition is stored as a unique row in table 'labels'.

labels
id   value
--------------------------
1    exhaustcleaningsystem
2    exhaust
3    cleaning
4    system
5    exhaustcleaning
6    cleaningsystem

I thought about a new table called 'compositions':

compositions
id   domain_id   range
----------------------
1    1           2,3,4
2    1           5,4
etc...

But storing multiple separated values in a column isn't normalized design. Any ideas for that?

BTW: I'm using MySQL und ActiveRecord/Rails.

Answer 1

The design you propose is not even in first normal form, since range is not atomic

The schema I'd use here would be

compositions
id   domain_id
-------------
1    1
2    1

compositions-content
composition_id        rank        label_id
------------------------------------------
1                     1           2
1                     2           3
1                     3           4
2                     1           5
2                     2           4

with composition_id referencing an composition.id and label_id referencing label.id

The rank column is optional and should be here if and only if the range you define here is order-sensitive.

With this design, you have some referential integrity at DB level.

Answer 2

Well, this is as far as I can think of in terms of normalisation:

sets
id   domain_id
--------------
1    1
2    1
etc...

compositions
id  set_id  label_id  order
---------------------------
1   1       2         1
2   1       3         2
3   1       4         3
4   2       5         1
5   2       4         2
etc...