The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Question about mapping of tables <-> classes

Suppose I have two related entities: school and student.

Table School:
  - id  (PK)
  - name
  - address

Table Student:
  - id  (PK)
  - fullName
  - email
  - schoolId (FK)

And their respective classes:

School class:
  int id;
  string name;
  string address;

Student class:
  int id;
  string fullName;
  string email;
  School objSchool = null;

I have a business layer (BLL) and a data access layer (DAL).

Whenever I need to get a student (or a list of students) I have two options...


option 1)

Have this code inside SchoolDAL:

public School GetById(int id)
  // "select id,name,address from School where id = " + id
  // build a School object from data reader
  // return School object

Have this code inside StudentDAL:

public Student GetById(int id)
  // "select id,fullname,email,schoolId from Student where id = " + id
  // build a Student (say, 'stu') object from data reader
  // build a School (say, 'scho') object and set its id 
  // stu.objSchool = scho;
  // return Student (stu) object

Finally inside StudentBLL do:

public Student GetById(int id)
  StudentDAL stuDAL = new StudentDAL();
  Student stu = stuDAL.GetById(id);

  // now I have a stu object
  // objSchool is a property/field of stu
  // inside objSchool I have only the "id" information
  // I need to get the whole School object, so...

  SchoolDAL schoolDAL = new SchoolDAL();
  School school = schoolDAL.GetById(;
  // link objects
  stu.objSchool = school;

  return stu;

option 2)

Have this code inside StudentDAL:

public Student GetById(int id)
  // using SQL JOIN:

  // "SELECT,,A.address,,B.fullName,,B.schoolId FROM Student A, School B WHERE = " + id + " AND = A.schoolId"

  // build both Student and School objects from data reader

  // stu.objSchool = scho;
  // return Student (stu) object fully populated

Inside StudentBLL do:

public Student GetById(int id)
  StudentDAL stuDAL = new StudentDAL();
  Student stu = stuDAL.GetById(id);
  return stu; 


Which one would you pick?

option 1 would be easier to maintain in case of database schema changes. (for example, if School table receives a new field - let's say principalName - i would have to change only SchoolDAL)

option 2 would be faster (better performance) but worse to maintain. (if School table receives the new field 'principalName' - i would have to change SchoolDAL AND StudentDAL - because of the SQL JOIN).

Any thoughts?
Saturday, April 29, 2006

Is this a high performance application?  If so for efficiency's sake you would bring everything down in one query (You should consider doing this anyways since inevitably you'll end up with some objects that want to load from 2+ tables anyways).

Also, don't hamstring yourself because a field "might be added".  Like any risk situation you have to determine just how likely it is that fields will be added frequently.  With today's code generators like codesmith if you break classes up nicely you can regenerate the stored procedures and DAL loads quite quickly and easily.

You'll end up with more queries, but it's possible to end up with the best of both worlds by exposing some overloads of the LoadById() method.  I've seen it implemented as follows:

LoadById(int key)  // loads just the student info by pk

LoadById(int key, bool Deep)  // if deep is true, then do a 'deep load' and grab all the join information and populate sub objects.

In this way, you can pick and choose to lazy load information where you want and still have the overload to pull back all the info at once if you're going to need it.
Eric Wise
Saturday, April 29, 2006
Everything you ever wanted to know about ORM principles, and then some:
Berislav Lopac Send private email
Sunday, April 30, 2006
+1 for  Berislav Lopac. 

That is a great article, you never really know the value of ORM and Design Patterns until you're going through the pain.  I recently thought, "hey I don't need anything like ORM just yet".  Then I just scanned through the article and found several answers to current roadblocks && challenges.
Sunday, April 30, 2006
Thas a great link Berislav Lopac thanks
Mr K Lash
Monday, May 01, 2006
I noticed that your code implies separate DAL objects and business objects - I'm not sure where that idea comes from, and I've seen it before, but it's kind of pointless in this case (and every case I've seen it used when the classes are hand-written) - we just make a single class which contains it's own DAL criteria (using shared code which allows you to change your underlying DAL mechanisms).

The benefit of two-classes mechanism is beyond me - I have discussed every scenario before when reviewing these designs and I cannot find any advantages.  We just make a DAL section within our business classes.

I would choose and improve Option 2 by removing the inline SQL code to a stored procedure.  The SP can be optimized and can be managed separatedly from the code, in the database where the rest of the database changes are located.

Just as you view the objects as offering a certain business interface to the application, which strictly uses the business object model you build, you should view the database as offering a certain interface to the business objects.  The database interface usually consists only of SPs in our case.  The accounts which we let have access to the database do not have any rights other than execute.

In addition, we do not always have business objects which correspond to tables.  Business objects only correspond to objects which the programmers need to program against.  Some other objects which might just be artifacts (like link tables) of implementation and design - are not exposed to application programmers as business objects.

And +1 for the article posted, which discusses lazy load/deferred evaluation, which we like to use a lot.
Cade Roux Send private email
Monday, May 01, 2006
Yeah, speaking of lazy loading, thats the problem with the join.  Sure, your bringing more data out at once, but you may not *need* it.  What if someone just wanted to see a list of schools? Or information on a specific school, but doesn't want to see the school's students?  Then the join becomes needless.  Just a thought, its all very context specific.
Vince Send private email
Tuesday, May 02, 2006
I agree: in general, the problem is context specific..

but as an apriori rule, if you don't have specific information about the queries that will be needed, I think lazy read policy is better for business objects..

There's always room to improve data access performance:
1. You can always use or add caching mechanisms
2. You can always add specific methods to map directly some particular queries.. (getStudentByCourse, getWhateverBy..)

So I think the lazy-read approach scales better rather than a "read ASAP as much as you can" approach....
Johncharles Send private email
Monday, May 08, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz