Grouping records with or without a foreign key constraint

Question

I have a table containing objects, something like this:

PK ObjectId
FK ObjectTypeId
Description
etc

The objects need to be grouped. I have been given a number of suggestions, all of which 'work' and some I like more than others. None of them are perfect so I'm struggling to settle on any particular model.

1/ Add a self-referential foreign key. This is clean but not ideal because (a) there is no logical parent (it's a group, not a hierarchy) and (b) it is potentially a pain for LINQ-to-SQL to traverse a self-referential hierarchy - would need to check to see if the current object is a 'parent' or a 'child' object, etc.

PK ObjectId
FK ParentObjectId

2/ Add a parent table & a foreign key. This adds constraints but the Group table doesn't contain any useful information - it exists only to provide a GroupId constraint & identity.

Table Object
PK ObjectId
FK GroupId

Table Group
PK GroupId

3/ Add a GroupId without a constraint or foreign key. No data integrity. The theory is that when a new object group is inserted, each object is given GroupId = the first assigned ObjectId. Probably the simplest & most practical solution.

e.g.

ObjectId  GroupId
...
15         10
16         16
17         16
...
21         16
22         22

My question is which of these is the best in theory and/or practice, and why. Or, please tell me a better way to do this! I personally like (2) because it is normalized, but am told that a table with just one field is bad design. Thoughts and suggestions?

Answer 1

For option two, you can always add additional fields, for discriptions, display orders, and GroupParentID, for multiple levels of grouping. Also, you can implement securit on these grouping structures.

In our application we use that structure.

Answer 2

From the limited info you present it is difficult to recommend an option, because the method depends on your usage of the tables. Storing the data is one thing, but using it is a completely different issue. All three store the data is a way possible to retrieve it, however what queries will you need to construct to load it, aggregate it, and search it?

I would implement each method, populate with a small set of data and try to write a few queries to gather data as your application would. Any problems or difficulties in using the table design will become apparent then.

You should always design your tables so data retrieval is fast and easy. You shouldn't have to fight your tables to get your data out. If do you find yourself fighting with your tables to get the data out, then you designed them poorly. Your table structure should make your life easier not harder.