Is one big domain table considered bad design? [closed]

Question

I have to add some features to a web application I'm maintaining, and I have to decide which path to follow to modify the database (Sql Server 2010) in order to store and retrieve the new data the application needs.

"Ugly but fast" path:
The web application already has this "Generic domain table" that stores different domains data that can be retrieved and filtered via Stored Procedure, specifying a Domain field.
Something like:

| Id         | Description | Domain       |
|------------|-------------|--------------|
| 001        |    Apple    |     Fruit    |
| 002        |    Peach    |     Fruit    |
| 003        |    Banana   |     Fruit    |
| A01        |    Yellow   |     Color    |
| A02        |     Red     |     Color    |
| A03        |     Green   |     Color    |


SP_GetDomainValues @Domain='Fruit'

The table already has the application layer to store and retrieve data without effort.
All I need is to create a database script to populate the table with the new records I need with the proper new domains.
I should add that this application has also several domains table each storing just one domain.

"Good but slow" path:
I have to create different tables, Stored Procedures and DAL methods to store and retrieve the data.

Personally I like the second approach for these two main reasons:

• Using the data in a query is much easier, as you join naturally to tables, not subsets of one big table

• Data can be validated using foreign key constraints very naturally, something not feasible if you have one table, perhaps named “genericDomain”. It isn’t that it isn’t totally possible, it is just messy to use constraints

I tend to think that if you don't have some sort of rigid ratio that can help to decide which way to pick, you'll end up to take the quick and dirty path every time.

In your experience, is the first choice a bad design or there are cases where it can be used without feeling too guilty?

Answer 1

I would just go with what's already there: Getting the job done by just adding data sounds fine to me, even if it's not "perfect".

Consider also that your "good" design requires schema changes, new code and testing, all of which are costly and carry risk.

Answer 2

As usual, it depends.

If the application is working, and has no likely functionality enhancements, you could just do the quick and dirty thing, and move on with your life. The business will likely thank you, because it's the fastest and most easily predicted route. You'll feel bad about leaving code in no better state than you found it, but you'll hopefully get to work on something more rewarding next.

If there are bugs in the application, or it's likely that you'll need to add even more features in the future, or if you are the long-term maintenance owner for this, you need to trade off the future pain of working with this model against the short-term pain of bringing it into a more maintainable state.

As @a_horse_with_no_name writes, the design you outline is a well-known anti-pattern. It is brittle - a change in the data can break the application in all kinds of exciting ways; it relies on lots of different layers all understanding the underlying data storage mechanism, and being able to remember the right word for "fruit" (including casing and spelling and any rogue spaces). It relies on application logic to check the validity of the data - there's no referential integrity - and in most cases, this fails somewhere so an innocent end user enters a value they think is right, but which breaks the rules in some way and is forever lost.

So, if you have to live with this code for a while, I'd consider refactoring it. I'd begin by writing unit tests and integration tests for the existing code, and try to gradually refactor parts of the application to use a more rational database model. Converting the entire application may well take as much time as the original build, and most business folk won't be happy to hear that adding a couple of simple extra features will require an entire rebuild - so look for pragmatic ways of getting to where you want to go!