In almost every application you have this look up entity that provides a dynamic references. This are things like type, category, etc. These entities will always have id, name, descSo at first I designed different entities for each look up. Like education_type, education_level, degree_type....But on a second thought I decided to have on entity for each of these kinds of entities. But when I am done with the design and check the relation this entity will be referenced by almost all entities in the system and I don't believe that is appropriate. So What is your take on this? Can you give me some clear pros and cons?
Designing a single look up entity
design;object oriented
To implement this approach, you need to have the following columns:TableID (or a categorization column)CodeDescriptionIsActive (to avoid delete)You will assign tableID=1 for education level, tableID=2 for degree type, etc.Issues:0 - Lookup values will not be easily sharable across projects. 1 - You need to build a Primary Key on the composite columns of (TableID and Code) to ensure uniqueness or have the code by sequentially generated column. This would lead to a composite FK which is not very nice. Alternatively, you could add an artificial column to be the PK (auto sequence for ex.).2 - Each SELECT will have a hard-coded tableID. This could lead to ugly bugs. 3 - Be ware not to loose relationships between entries. For example, eduction level is related to degree type. 4 - Your data model (if you care) will not have meaningful relationships with this look-up table since some of the entries will not apply.More on that can be found in: Why is using a common-lookup table to restrict the status of entity wrong?In summary, if you have a small application, this may be tolerable, but if you are developing a large application it is not OK to have several look-ups in 1 big look-up table, at least conceptually.