SQL repeating sets

I have the need to store sets in an RDBMS, but SQL doesn’t lend itself well to repeating elements.

For example, picture having to store a bunch of name/value pairs on a person. Initial try may conclude a Non-First Normal Form of type

Person	Skills
Alice	c=ok;java=good,verifiedBy=Sun,verifiedDate=[timestamp]
Bob	c=good;c++=excellent
Mallory	c=good;hacking=excellent,verifiedby=cloodyHatSociety

This of course doesn’t scale at all. And parsing weird name/value pair lists inside a string? You might as well store it as an XML blob.

If you’re stuck with a relational database, you may want to break it up at least till 3NF. I have recently done a lot of Ruby on Rails and have come to like the auto increment primary key counter Rails uses everywhere.

Id	Person
1	Alice
2	Bob
3	Mallory

Users, primary key Id

Id	Statement	Verifier	Verification date
1	S1	null	null
1	S2	V1	timestamp
2	S3	null	null
2	S4	null	null
3	S3	null	null
3	S5	V2	null

Verifications, primary key (Id, Statement)

Id	Name
1	V1	Sun
2	V2	cloudyHatSociety

Verifiers, primary key Id

Id	Name	Value
1	S1	c	ok
2	S2	java	good
3	S3	c	good
4	S4	c++	excellent
5	S5	hacking	excellent

Statements, primary key Id

This seems a very simple and usable solution.

Comments

[Melissik]

One afternoon, I was in the backyard hanging the laundry when an old, tired-looking dog wandered into the yard. I could tell from his collar and well-fed belly that he had a home. But when I walked into the house, he followed me, sauntered down the hall and fell asleep in a corner. An hour later, he went to the door, and I let him out. The next day he was back. He resumed his position in the hallway and slept for an hour.
This continued for several weeks. Curious, I pinned a note to his collar: "Every afternoon your dog comes to my house for a nap. "
The next day he arrived with a different note pinned to his collar: "He lives in a home with ten children - he's trying to catch up on his sleep."

I cried from laughter
Sorry, if not left a message on Rules.