Database model to object oriented design?

How would I design classes say in c# to represents a database model?

Given the f开发者_StackOverflow社区ollowing tables and fields for a database,

Table: Employee

Pk EmpID
   Lname
   Fname
   Adress
Fk DeptID

Table: Department

Pk DeptID
   DeptName
   Location

Ok so now I want to make 2 classes in C#, one for Employee and one for Department. The part I get hung up on is the foreign keys. Should I use the foreign keys in my design for the objects, or should I put a reference in my Employee class of the Department,or should I put a list of employee references in my Department class,or should I do both? I know if I use foreign keys, it will be less efficient because I will have to search through a list of primary keys that match the foreign key but I probably should include them in the design anyway.

Don't use "foreign keys" in your objects. Use references; each Department should have a list of Employees. Depending on whether or not you have a need to backreference from Employee to their Department, make the determination as to whether Employee will have a reference to Department.

Elaboration on @Paul Sonier answer...

P.S. I'm using business layer, business classes in a general sense, not as jargon of some particular technical design pattern.

The specific problems of using database keys
Using database keys is going to cause an explosion of coding overhead to keep objects and the database in synch. As the need to add, change, delete objects happens (via user GUI) you'll be jumping through hoops like crazy. How would you create a child object when the parent object does not exist yet in the database? Imagine trying to do this with any N-level data structure.

Always design business classes without regard to data storage
Business layer classes should faithfully reflect the business rules, jargon, concepts, and context. Polluting this "idea space" with non-business stuff with details about storing or displaying data is bad in the long run. Hear me now and believe me later.

Business classes based on some particular database table layout (and it's keys, etc.) is going to make it disastrously difficult to write code for validating rules, creating proper state of those objects, and so on. This is on top of the problem of keeping the object IDs in synch w/ the database.

Maximize Decoupling of Business layer and Data layer
The example shown in your question is a tempting deception. Some of your business classes may map very well to your database design. And consequently the primary and foreign keys may seem to fit as well.

BUT in any non-trivial application the database model will deviate. If not now, later. It will deviate for the purposes of database integrity, efficiency, and speed. And what does this have to do with the business model? Nothing.

Indicators that you're doing things right

1. You can instantiate a business object without an existing database

2. Each object can reference it's "children" without requiring special keys created outside the business class model.

3. Each business object on its own can validate, enforce, flag, etc. all of it's own rules, even trivial ones like "can't be blank". Business rule validation for composite classes/objects is class design & analysis activity - not database design activity.

First, if possible I would suggest using a tool like NHibernate, the Entity Framework, or Linq to SQL to do the object to relational mapping for you. But, if you don't want to do that, I'd probably design my object model like this:

public class Employee
{
    public int Id { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public Address Address { get; set; } 
    public Department Department { get; set; }
}

public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Address Location { get; set; }
    public ICollection<Employee> Employees { get; set; }
}

I don't want to oversimplify but if you have navigation properties from Employee => Department or Department => Employee is pretty specific to the needs of your application.

However, as a rule of thumb, I tend to put navigation properties from the top of the heirarchy down. That means I would have Department.Employees but not Employee.Departments.

Again, this is specific to you, but it seems pretty unlikely that you would ever need to get the Department object from every single Employee. So, lookup with a key inside the Employee class something like this:

class Employee {

    public int[] DepartmentIds { get; set; }
    public List<Department> Departments {
        get {
            return YourStaticReference.DepartmentList
                .Where(x => this.DepartmentIds.Contains(x.DepartmentId));
        }
    }
}

See? Best of luck!