Modeling Classes Based on Table Designs

Question

Is this how one would normally design classes? One class = 1 Table. How about tables that contain a foreign key to another table?

Suppose I have the following:

PersonTable
---------------
person_id
name

PersonMapTable
---------------
map_id
type_id (fk)
person_id

PersonTypeTable
-------------------
type_id
description
parent_type_id

AddressTable
-------------------
address_id
address1
address2
city
state
zip

AddressMapTable
-----------
address_map_id
address_id
person_id

Would good practice consist of creating a class for each table? If so, what are the best practices for loading/saving such classes back to the database without an orm? A simple code example would be really helpful

Thanks

Answer 1

I don't think that one object per table is necessarily a good design. It's hard to give a one size fits all rule, but objects can be richer and more fine grained. A database can be denormalized for reasons that don't apply to objects. In that case, you'd have more objects than tables.

Your case would include 1:1 and 1:m relationships:

public class Person
{
    // 1:m
    private List<your.namespace.Map> maps; 
}

public class Map
{
    // 1:1
    private your.namespace.Type;
}

Answer 2

I wouldn't say that I always have class per table, especially when you have many to many relationships. Based on your table above I would have 2 classes... I am not sure why you have both and id and a person_type_id, to me they would be the same thing but here are the classes.

Person
{
   public int Id { get; set; }

   public string Name { get; set; }

   public List<PersonType> { get; set; }
}

PersonType
{
   // I would discourage from using Type as property name as it is a keyword...
   public string [Type] { get; set; }
}

Answer 3

I'd recommend reading Martin Fowler's Patterns of Enterprise Application Architecture, which has several patterns of mapping between classes and tables.

Answer 4

Unless your users are data entry clerks, it's generally considered better to design classes from Use Cases/User Stories. Even if the database already exists.

Reason? It's too easy for users to end up assuming their job is to exercise the software, rather than expecting the software to help them do their jobs.

Clearly they need to intersect at some point. I concur that Fowler's book is a great place to start. But I think he'll reinforce this point of view.

If you want a modeling perspective that helps you get both the classes and the database right, consider Object Role Modeling.

Answer 5

For the most part I tend to map tables to entities, but it's not a hard rule. Sometimes there are instances where the repository for a specific entity in question is better off dealing with the general concerns surrounding a specific entity, which means it will cross into dealing with other tables as a result, without those tables specifically needing to exist as entities.

What I never do (except in very specific planned cases where the dependent data ALWAYS needs to be retrieved with the entity), is set an entity or collection of entities as a property of another entity. Instead, that entity will either be discoverable via its ID, which will either be a property of the parent entity or discoverable via the associated repository in relation to the parent entity.

In cases where I need the child entity or entities of another entity to be bundled together, I'll make use of an "info" helper class to pull together all the required data. For example, if I have an entity class Widget and it has a collection of child Part objects, then I would create a WidgetInfo class which would contain the Widget instance as a property and a collection of Part objects as the other property.

This way all entity classes remain as lightweight as they can and never make the assumption that dependent data will need to be loaded. Also it keeps the repository model clean without forcing you into messy ORM territory which is generally the case if you create child object collections on an entity class. If you do that without ORM, then you end up with the messy problem of when to load the children or not, and when to assume that the children have or have not been loaded.

Answer 6

If you plan on using Object-Relational Mapping (ORM), this may affect your table design. Hibernate, for instance, does not like mixed inheritance mapping strategies within the same tree.

Since you specifically indicated that you will not be using ORM, you can follow traditional database design principals. This typically means starting with one table per class, normalizing to third-normal form (read about database normalization here), then denormalizing to meet performance constraints (read about denormalization here).

Regarding your question about how to load and save the objects without the use of ORM, a common strategy is to use Data Access Objects (DAOs). Here is a simple example:

public interface ICustomerDao
{
  public void insert(Customer customer) throws CustomerDaoException;
  public void update(long id, Customer customer) throws CustomerDaoException;
  public void delete(long id) throws CustomerDaoException;
  public Customer[] findAll() throws CustomerDaoException;
  public Customer findByPrimaryKey(long id) throws CustomerDaoException;
  public Customer[] findByCompany(int companyId) throws CustomerDaoException;
}

You didn't specify which language you are using, but regardless you may find this example using Java generics for DAO useful.

Answer 7

Would good practice consist of creating a class for each table? If so, what are the best practices for loading/saving such classes back to the database without an orm?

You are using ORM. You are mapping objects to relational tables. Whether you use a pre-built library to do so or not is your call. If you don't, you'll be essentially implementing one yourself, though probably without all the bells and whistles of existing ORMs.

The two most common ways of doing this are the ActiveRecord pattern and the Data Mapper pattern. Each has its advantages and disadvantages.

With the ActiveRecord pattern, you define classes whose attributes define the table columns for you. Each instance of this class corresponds to a row in the database, and by creating (and saving) a new instance, you create a new row in the database. More information on that is available here: http://en.wikipedia.org/wiki/Active_record_pattern

In the Data Mapper pattern, you define table objects for each table, and write mapper functions which assign columns of the table to existing classes. SQLAlchemy uses this pattern by default (though there are ActiveRecord type extension modules, which adapt SQLAlchemy's functionality to a different interface. A brief introduction to this pattern can be found in SQLAlchemy's documentation here: http://www.sqlalchemy.org/docs/05/ormtutorial.html (read from the beginning up to but not including the section entitled, "Creating Table, Class and Mapper All at Once Declaratively;" that section explains ActiveRecord using SQLAlchemy).

The ActiveRecord pattern is easier to set up and get working with, and gives you classes which are clearly representative of your database, which has benefits in terms of readability. As a side benefit, the declarative nature of ActiveRecord classes effectively acts as clear and straightforward documentation for your database schema.

The Data Mapper pattern gives you far more flexibility in how your data maps to your classes, so you aren't tied to a more-or-less one-to-one relationship between tables and classes. It also separates your persistence layer from your business code, which means that you can swap out other persistence mechanisms later, if need be. It also means you can more easily test your classes without needing to have a database set up to back them.

For more in depth discussion of SQLAlchemy's mapper configuration, check out http://www.sqlalchemy.org/docs/05/mappers.html. Even if you aren't planning on using a library like SQLAlchemy, the documentation should help you see some of the options you may want to consider in mapping your classes to database tables.

Answer 8

This DataBase to Class point of view approach is more than likely to lead you to cranking lots of code quite quickly. However, a good chunk of this code will likely not be of any use or require some severe mutation. In other words, you'll likely build specific classes that mismatch your displays and workflow.

First, consider your apps, your users' needs, general workflow .. etc Actually come up with something that looks workable (i.e. mock your displays).

Concentrate on the classes you need to use the displays and model your storage (db design) after those needs. Chances are that you will have only a few of straight-table classes as most of your classes will naturally tend provide the solution for your displays.

Good luck.