Database design Pattern Name Question (I think it's called the Singleton Pattern)

Question

Hi,

I've got a table in my database where there can only be 1 row per non-identity attribute tuple. Whenever we insert a row, we first check to see if a row exists with those values, if not, we insert it, otherwise we use the ID from the existing row.

In the example below, there would be a Unique Index on (Id) and (Attr1, Attr2, Attr3)

+----+-------+-------+-------+
| Id | Attr1 | Attr2 | Attr3 |
+====+=======+=======+=======+
| 1  |  A    |  A    |  B    |
| 2  |  B    |  A    |  B    |
| 3  |  C    |  A    |  B    |
| 4  |  D    |  A    |  B    |
| 5  |  E    |  A    |  B    |
+----+-------+-------+-------+

What is this pattern called?

Answer 1

It's called First Normal Form: Table faithfully represents a relation and has no "repeating groups"

Answer 2

There's no particular name for this pattern.

You have a natural primary key candidate on Attr1, Attr2, Attr3 and a surrogate primary key candidate on ID. You can make one the primary key and create a unique index on the other. You can choose either for clustering depending on your needs (I would first consider the surrogate).