views:

686

answers:

5

In working with objects and interfaces what is the best practice for writing to the database? There is a plethora of opinions regarding the object design but I am unclear about the database end. A simple example:

Suppose a Contact base class that contains the common fields such as contact name (Bill, Fred, Sally) and a location (home, work, etc.). Add an IPhone interface (area code, phone number, extension) and an IEmail interface (email address,cc) to abstract out the differences. Then create classes (Phone, Email) that inherit from these as thus:

    Phone: Contact, IPhone 
    Email: Contact, IEMail

An alternative would be to create an IContact interface instead of a Contact base class as thus:

    Phone: IContact, IPhone
    Email: IContact, IEMail

Short of implementing NHibernate or Entity Framework what is the best practice for the data access code if these objects are being written to a single database table? What I have seen seems rather clumsy.

+3  A: 

It is my experience that when it comes to databases, good relational design has to come first if you want to have any semblance of performance. After that, my model is quite simple: one table, one class. If I have a self referential relationship, I create another class deriving straight from the original. Joins are defined as lists of objects (for example, order has a list of order details).

Once I have this down, I use a DAL capable of reflecting upon these classes and generate the required SQL on the fly; after profiling I might have to create some SP's but its results are also sent to a receiving DTO.

I didn't encounter any reason to use interfaces so far.

Otávio Décio
Interfaces server an excellent role in the DAL. You might consider interfacing all of your object services and referring to those by interface reference. It will make it easy to mock out, and if you do need to scale or remote later you will have an much easier time doing so.
Jason Jackson
That is true, I hadn't thought of the mocking and remoting aspect of it.
Otávio Décio
A: 

I guess you need to consider the relationship between these three entities. Say the following statements out loud and see which is true for your application:

  • Phone IS A Contact
  • Phone HAS A Contact

Just from what you have posted here it sounds like you can greatly reduce the complexity of your application by going with the second option.

However you are wondering about data access and you mentioned that there is a single database table which makes me wonder why you need separate entities for Contact, Phone, and Email. I would recommend that you either refactor your code to include a single entity to represent your data model as this will reduce the complexity of your codebase or refactor your database schema to reflect what it seems like you really want (based on your sample code).

Andrew Hare
Actually, I would think of it the other way around: a Contact has a Phone.
tvanfosson
That is what I was thinking at first too but his data model seems to be the other way around.
Andrew Hare
+1  A: 

Build your database first so you are sure you can actually store everything you need. Then build common queries like "All customer's and their primary phone number" and "A single customer with all their phone numbers".

This will expose obvious like not having enough phone number fields or not having a way to indicate the real phone number.

Your database is going to last at least three times as long as your application and is hard to change, so make sure you get it right from the beginning.

Jonathan Allen
Absolutely, getting the db correct is really important.
Otávio Décio
+1  A: 

It sounds like what you are trying to model are phone contacts and email contacts, but I think what you really have are contacts with a primary contact method: phone or email. The way I would structure it would be with composition -- assuming that you want the flexibility to have multiple phone numbers/emails. If you can live with a single one of each, you could store it directly in the contacts table. If you need multiple addresses, then handle it the same way as phone/email with a separate table.

Contacts Table
     ContactID
     FirstName
     LastName
     MI
     StreetAddress1
     StreetAddress2
     City
     StateProvince
     PostalCode
     PreferredContactMethod (0 = Phone, 1 = Email)
     ... more details ...

PhoneNumbers Table
     PhoneID
     ContactID
     PhoneNumber
     IsPrimary
     ...more details...

EmailAddresses Table
     EmailID
     ContactID
     EmailAddress
     IsPrimary
     ...more details...

For your classes then, you would have a Contact class, which contains one or more PhoneNumbers and one or more EmailAddresses. If you had other things than Contacts that had phone numbers or email addresses, then it might make sense to have interfaces indicating that they are IPhoneable or IEmailable -- meaning just that you could add/remove phone numbers or email addresses from them, like so:

public interface IPhoneable
{
     public PhoneNumber GetPrimaryNumber();
     public void AddNumber( PhoneNUmber number );
     public IEnumerable<PhoneNumber> GetNumbers();
}

public interface IEmailable
{
     public EmailAddress GetPrimaryAddress();
     public void AddEmailAddress( EmailAddress address );
     public IEnumerable<EmailAddress> GetEmailAddresses();
}

public class Contact : IPhoneable, IEmailable
{
     private List<PhoneNumber> phoneNumbers;
     private List<EmailAddresses> emailAddresses;

     public int ContactID { get; private set; }
     public string FirstName { get; set; }
     ...

     public Contact()
     {
         this.phoneNumbers = new List<PhoneNumber>();
         this.emailAddresses = new List<EmailAddress>();
     }

     public PhoneNumber GetPrimaryNumber()
     {
          foreach (PhoneNumber number in this.phoneNumbers)
          {
               if (number.IsPrimary)
               {
                    return number;
               }
          }
          return null;
      }

      ...
}

If you wanted, you could have a PhoneableContact and an EmailableContact that would subclassed off Contact and be differentiable based on the preferred contact method, but I don't really see a need for this. If you did that, then Contact wouldn't implement either interface -- the subclasses would implement the appropriate ones respectively.

tvanfosson
A: 

In this case... i think that the best option is to use a database object, like DB4O.

"Db4o is the open source object database that enables Java and .NET developers to store and retrieve any application object, including Topic Maps, with only one line of code, eliminating the need to predefine or maintain a separate, rigid data model."

http://www.db4o.com/

Narven