Application Design - Database Tables and Interfaces

Question

I have a database with tables for each entity in the system. e.g. PersonTable has columns PersonId, Name, HomeStateId. There is also a table for 'reference data' (i.e. states, countries, all currencies, etc.) data that will be used to fill drop down list boxes. This reference table will also be used so that PersonTable's HomeStateId will be a foreign key to the reference table.

In the C# application we have interfaces and classes defined for the entity. e.g. PersonImplementationClass : IPersonInterface. The reason for having the interfaces for each entity is because the actual entity class will store data differently depending on a 3rd party product that may change.

The question is, should the interface have properties for Name, HomeStateId, and HomeStateName (which will be retrieved from the reference table). OR should the interface not expose the structure of the database, i.e. NOT have HomeStateId, and just have Name, HomeStateName?

Answer 1

I'd say you're on the right track when thinking about property names!

Model your classes as you would in the real world.

Forget the database patterns and naming conventions of StateID and foreign keys in general. A person has a city, not a cityID.

It'll be up to your data layer to map and populate the properties of those objects at run time. You should have the freedom to express your intent and the representation of 'real world' objects in your code, and not be stuck to your DB implementation.

Answer 2

Either way is acceptable, but they both have their pros and cons.

The first way (entities have IDs) is analagous to the ActiveRecord pattern, where your entities are thin wrappers over the database structure. This is often a flexible and fast way of structuring your data layer, because your entities have freedom to work directly with the database to accomplish domain operations. The drawback is that when the data model changes, your app is likely to need maintenance.

The second way (entities reflect more of a real-world structure) is more analagous to a heavier ORM like Entity Framework or Hibernate. In this type of data access layer, your entity management framework would take care of automatically mapping the entities back and forth into the database. This more cleanly separates the application from the data, but can be a lot more plumbing to deal with.

This is a big choice, and shouldn't be taken lightly. It really depends on your project requirements and size, who will be consuming it.