Single column/primary key only table for referential integrity?

Maybe i'm going about this wrong but my working on a database design for one of my projects.

I have an entity with a classification column which groups up entities into convenient categories for the user. These classifications are predefined and unchangeable by the user (at least thats the current design).

I'm trying to decide if I should have a 'EntityClassification' table which contains simply an 'Id' column as the primary key with no other information in order to have an enforced relationship between the Entity:Classification -> EntityClassification:Id.

I don't plan to have a name/description column in EntityClassification since my current thought is that I'll need to support localization of these pre-defined names which will be done with static string table like resource files downloaded to the client based on their country/language. There really isn't any other data which is associated with this EntityClassfication that I would want and a table seems like it might be an overkill?

Is this common/recommend practice for this type of problem? We're using SQL Server 2008 and don't have an enum datatype for the database which would seem to be really what i'm trying to achieve.

Sorry if i didn't make it clear.The classifications are predefined numbers which have predefined meanings. 1 always will equal group X. 2 will always equal group Y. Group X or Group Y might become Gruppe X in german for example and I'm planning of the client downloading a separate string resx with their language/country specific strings. Silverlight will have the meanings and show the appropriate one to the user. They'll never see the number and putting all the strings in the database for developers to see doesn't seem necessary, they'll have the string table.

MerickOWA 2010-09-15 15:28:03

right, so you want the internal staff (especially new hires in the future) to know that when someone says 'my x query doesn't work!' they mean queries with ID=1. The purpose is not for end users, but internal support staff who aren't around now for this mapping info you have.

Beth 2010-09-15 15:30:57

+1 to Beth. It bears repeating that the application is not going to be the only entity using your database and that entity may not know what the ID means. Localization issues aside, all IDs should ultimately be tied to something "human-readable" in the database. Otherwise they are useless outside the application, making internal support or ad-hoc business reporting impossible.

Matthew Wood 2010-09-15 17:04:23

Every inexperienced developer wants to break the Relational Model because surely the work of billions of person-hours of developing millions of databases over the last 40 years is surely filled with mistakes and errors, tons of inefficiencies and vestigial constructs. My "app", "middle tier", "res file", "data access layer", "poodle" can certainly be more "efficient", "useful", "responsive" at "constraint enforcement", "referential integrity", "indexing", "grouping", etc

Stephanie Page 2010-09-15 17:58:43

ansaurus

tags:

views:

answers:

Single column/primary key only table for referential integrity?

related questions