How should I approach this from a problem-solving point of view?

Question

I'm currently working on a project to replace some excel worksheets. There is a lot of industry specifics that change quite often and nobody really knows how things should be but every sure enough thinks they know how they shouldn't be.

Project Priorities:

• Maintainability - The business processes change frequently. There are like 3-5 different excel templates floating around.
• Agile - My boss wants visible changes every week (sometimes less).
• Structure - This application will work with several other systems for the entire business process.

Approach & Design:

The DB Schema we took was one where we defined some business logic in the database. EntityTypes, Properties of those types, and their values are stored in the database (each having their own table ofcourse).

Now, I originally thought about the typical approach, having an Estimate Table defining its properties, Bid Table, Joist Table, etc. (Each of these are EntityTypes by the way).

My boss wanted the DBSchema we're using now (See picture).

Pros/Cons:

Current Schema:

• Pros: (Theoretically) Adapts to the data allowing for later changes to be as simple as adding the data to the database.
• Cons: Overly complex. I have difficulty even getting my mind around this schema when trying to create the class that will handle it.

Typical/Traditional Schema:

• Pros: Simplistic, Anyone without knowledge of the project will be able to pick it up in the future.
• Cons: Many Many Tables. Not what we have now.

Situation:

I am currently retrieving the data using a class I made to represent an entity, then converting them into business model classes (Estimate, Bid, Joist...) to pass to the view. This obviously is extra work for the current schema, but I felt that the other way of having the view/controller parse through the model to determine how to display the data (making us only require a few views/controllers) seemed like bad design/programming (though I'm not entirely sure why). Am I correct thinking this schema/design is bad? If, so why?

My Boss wants this Schema because "it may be complex but once the programming is done no more changes should need done." Now obviously the way I'm doing this, that's not true. If I were to do it the way he wants I feel like I might fail to do so as I kept confusing myself with the complexities.

One of my boss's arguments is that you find yourself with a lot of NULL fields when you do the traditional schema (which I feel like you're doing it wrong if that's the case. Maybe in some instances but they have tables with like 30 columns where 10+ majority of the rows have a NULL value for that column).

Will doing all the programming for supporting the current Schema the way he desires be worth the theoretical simplicity to add things in the future? It's not all that much work to add a small feature to a class, and add the display to the view's template using knockout. Especially considering the project is being done in an Agile environment and doing all the work at once then having something to show for it doesn't sound very Agile to me, which is basically what would need done to code the complex classes and display them in the views, not to mention I'd have to code a bunch of HTML helpers (coupling them a bit with Knockout) in order to do this. So personally it sounds like it's just moving the work not saving any. Am I correct in this thought process?

Proposed solution:

I suggested the other day to switch to the other schema and use Entity Framework Code-First to make changes easier (as the way I'm doing it now I have the change the Business Models regardless. Code-First would theoretically allow me to not have to do the Database Changes as well).

My boss's main concern is that with a traditional schema the schema changes and then other systems will need updated as well. Whereas, he states that if we have the current schema then the other systems won't need updated if something changes in the database. Which I don't quite understand since how would it know how to deal with the data? Wouldn't it just mean it too would need complex coding/processes to back it up initially?

Questions:

• Why is this schema design bad (if it is)? How can I argue this schema design is bad?
• If it is not a bad design, How can I better prepare myself for it? I do my best to Abstract classes/functionality and keep things with single responsibilities.
• If it is a bad design, How can I convince my boss to switch to my suggestion (assuming my suggestion is the best/only alternative)?

I understand the last two questions are a but subjective, so it's understandable if they are ignored.

Answer 1

To be honest excel spreadsheets are a better solution than the proposed database.

Maybe you could recommend a hybrid approach to your boss.

• Its unlikely that you will get a new Entity (i.e. basic object type) that often so have a table for each basic object type.
• Each Entity/Object will have a number of attributes/fields which are basic to that object and will never change -- define these as columns in your table.
• Each entity will however have Attributes/Fields which are highly volatile depending on the current business requirement -- leverage the XML capabilities of any modern DBMS and store these within XML columns which can happliy hold key/value pairs, hash tables, object trees or all manner of structures without requiring a change to the underlying database schema. If you do not like XML a CLOB string holding a JSON object can work just as well.

At the very least if your boss insists on the current proposal get a NoSql database which is designed for this sort of thing.

Answer 2

This is a very common design mistake, most people go through this phase :)

Why is this schema design bad (if it is)? How can I argue this schema design is bad?

It's not taking advantage of a relational database:

• No type safety, boom.
• No foreign keys, boom.
• No referential integrity, boom.
• Very difficult to query, boom.

One massive table to store everything will make things slower and more difficult.

How can I convince my boss to switch to my suggestion (assuming my suggestion is the best/only alternative)?

The age old question. Be prepared to spend some time on convincing him, but he may never change their mind. Do some googling and look up article like this one https://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes and maybe you can do some convincing :)

Answer 3

The schema as presented is representing an object model in the database - nothing inherently wrong with that, but it hides the semantic structure of the application...

This kind of thing is common in engineering applications, especially those that originated as complex spreadsheets: everything is reduced to a set of variables and categories and values, so you end up with one 'variable value' table for all the data, resulting in extraordinarily complex queries to retrieve logical entity rows

It appears that the application domain is underspecified; all of the information about the entities and properties that would form the domain schema are buried in the data in the object-model schema.

That makes it incredibly hard to understand the true (semantic) structure of the system, and thus anticipate and apply changes.

ADDENDUM:

The semantics of the system (the object modeling the application/business domain) are hidden within the objects stored in the database - it's a meta/knowledge system. Great for flexibility, difficult to understand without additional tools/effort.

For example, to understand the class structure of a particular area you can't just look at the code for the classes because there isn't any, and the structure of the database tables reveals nothing because they're generic. You'd have to query out the classes and properties and relations and trace through the data to understand what's happening in the application.

If the application model is extremely volatile and/or varied, this may still be the best solution.

Suggestion: query the current class/property structure out of the database and generate the equivalent code (or pseudo-code) representation, and see what the application domain really looks like right now. Imagine a more direct representation (e.g. one table per class) and think about how that database structure would have to change - and how often - to accommodate future directions. You're trading representational complexity for flexibility, this may help you be certain that it is (or is not) a good trade.

Answer 4

Once I see names like property and propertyValue I usually consider it to be reinventing / reimplementing a key-value data store but in sql.

I can see the argument that this generic approach seems easier to maintain.

However I think you can also make the argument that a little hard coding is code as:

• Code can be self-documenting
• Comments can be avoided (and so they'll never be out of date with the code!)
• tests can be self documenting and read well.