0

I have the following scenario:

An Atom Entity is usually unique and can be stored in the database using a single AtomID field as its primary key.. In rare cases theAtomneeds to be split. In that case a second record must be created in the database table with an identicalAtomIDbut a discriminatingSplitID`.

So, basically I need a composite key of AtomID and SplitID where the SplitID will rarely be discriminating. I would hate to lose the AutoID feature that the SQLServerCompact4 database offers, but that is what happens when I define a composite key for the Atom Entity in the Fluid API.

What is the best approach for this problem?

Dabblernl
  • 15,831
  • 18
  • 96
  • 148
  • "a second record must be created in the database table" You mean the Atom table? So the split has the same fields? – Gert Arnold Dec 09 '12 at 20:45
  • When the table is originally populated or added to, the `Atoms` are unique. An error later on may however cause the necessity to splt the atom in two different records, which of course will have differenet values for their other fields. But is paramount that I can see that the records once were one. – Dabblernl Dec 09 '12 at 21:14
  • I don't see any other way than a self-referencing FK. – Gert Arnold Dec 09 '12 at 21:18

1 Answers1

1

You should create a new table with foreign key , AtomID and the discriminating SpiltID to show the relation between the two.

Bhushan Firake
  • 9,338
  • 5
  • 44
  • 79
  • Wouldn't that require using a Join every time I want to query the Atoms table? That seems inefficient given that an Atom will rarely be split. – Dabblernl Dec 09 '12 at 19:19
  • I implemented it this way. We will see what it does to performance. – Dabblernl Dec 10 '12 at 08:06