Help with class object (relationship)

select car.*, null, null from car where carID not in deptRef table and carID not in empRef table
union
select car.*, deptRef.deptName, "Department" from car inner join the deptRef and dept tables
union
select car.*, empRef.empName, "Employee" from car inner join the empRef and emp tables

You can map that with Dapper. Although depending on the size of those tables it might not be the most efficient query (or the best approach). Maybe using case statements will be better. Meh.
 
Hamster said:
select car.*, null, null from car where carID not in deptRef table and carID not in empRef table
union
select car.*, deptRef.deptName, "Department" from car inner join the deptRef and dept tables
union
select car.*, empRef.empName, "Employee" from car inner join the empRef and emp tables

You can map that with Dapper. Although depending on the size of those tables it might not be the most efficient query (or the best approach). Maybe using case statements will be better. Meh.
Click to expand...

His design is not efficient, so using queries like that :sick: are just going to open a world of hurt.
 
semaphore said:
His design is not efficient, so using queries like that :sick: are just going to open a world of hurt.
Click to expand...
This is true. Not sure if he has access or authority to change table though.
 
semaphore said:
An employee can belong to a department, and a car can have a link to an employee. If you want a car can have a link to department as well i.e. sports cars. Like i said when you get a chance post the ERD. It just appears your db design is very convoluted.
Click to expand...


I think the department is a little misleading/confusing. I should have called it "Site". The employee doesn't belong to this site table in this scenario, hence I'm treating the employee and department/site separately - ignorant of each other.
 
Hamster said:
Snip.
Click to expand...


I have separate queries for cars belonging to employees and card belonging to department/site

I will will merge the two result sets to have an overall view of who or what site has what car
Hamster said:
This is true. Not sure if he has access or authority to change table though.
Click to expand...
They are currently working on excel and every month there is a different spreadsheet due to cars moving from one employee or site to another. So I'm designing the DB. I have a feeling the design might change as new info comes
 
Last edited:
Fuma said:
I have separate queries for cars belonging to employees and card belonging to department/site
I will will merge the two result sets to have an overall view of who or what site has what car
Click to expand...
Your table design won't enforce those rules though (max one optional owner), neither will that query. You'll have to either change the table design (which will limit future changes and cause headaches later) or enforce it in code, iow. after you got the results back you'll need to run the collection of cars through a validate function and handle/clean/error it.
 
Hamster said:
Your table design won't enforce those rules though (max one optional owner), neither will that query. You'll have to either change the table design (which will limit future changes and cause headaches later) or enforce it in code, iow.
Click to expand...

True. At the moment before I insert or update I do an IF NOT EXISTS check on either reference tables to make sure the the car hasn't been assigned to either employee or site.
 
It looks like I missed the whole mapping's implementation, more especially Dapper's mapping extension (dapper.mapper).

I need to name my class member exactly as my concrete class name.
Going back to my car class, if I want the following code to work
var cars = Conn.Query<Car, Employee>(
"select statement",
splitOn:"CarId",
commandType: CommandType.Text).ToList();

Or

var cars = Conn.Query<Car, Department>(
"select statement",
splitOn:"CarId",
commandType: CommandType.Text).ToLost();

the Employee and Department properties must exist in the car class.
So I can't have:
public Owner Owner {get;set;}

Instead it must be:
public Owner Employee {get;set;}
 
psyc said:
A simplie way to do it would be to have a property in the car object say assigned and set it to false. When it gets assigned check that assigned is not equal to true then change it to true otherwise throw an exception.
Click to expand...

To me this seems like the obvious solution. Why complicate matters unnecessarily?
 
Have not read the whole thread but:
Several ways you can do it but the class is supposed to implement the logic. You also have to think of whether you want the domain model to drive your DB design or have the relational DB drive the design. But here is how you can do it:

public abstract class Entity
{
long Id { get;set;}
}

public class Employee:
Entity
{
}

public class Department:
Entity
{
}


public class Car:
Entity
{
public long? AssignedEmployeeId { get; }
public long? AssignedDepartmentId { get; }

public AssignTo(Employee employee)
{
assignedDepartmentId = null;
assignedEmployeeId = employee.Id;
}
public AssignTo(Department department)
{
assignedDepartmentId = Department.Id;
assignedEmployeeId = null;
}
}

The Car class enforces mutual exclusivity of Employee / Department with the AssignTo methods. Remember that the Id properties only have getters,. If you give them setters then any outside class can violate the rule
 
Hamster said:
What?! Dapper is the shiznit unless you do fancy crap. Fast and it doesn't interfere too much.
Click to expand...


Lol. I haven't used vanilla ado.net since I started using dapper. EF doesn't do it for me.
 
You could use MVC. Your base classes should simply represent your database tables without the relationships. Then have controller and view classes to handle the relationship and views.

Some organisations don't even have foreign key constraints in the database(e.g. some Siebel public service software)
 
Last edited:
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X