Hi

I'm currently testing LLBLGen for an app I'm re-writing in .Net (from VB6) as I want to separate out the DAL which was previously wrapped up amongst the GUI layer.

As I'm pretty much starting from scratch I'm having to adapt my thinking/approach on all aspects of the development and so am re-looking at the DB design as well incase any adjustments need to happen here (which won't be too much of a problem at this stage).

One of the things that's been highlighted so far with my initial run-throughs with llblgen is the relationships between tables and I suppose I'm just after a second opinion to reassure me (or otherwise!) that my current approach is the best approach (or not as the case may be!).

Anyway, the basic design so far is...

Customer table (with all the obvious details stored here)

Contacts table (again, all obvious info stored here) - related to Customer table through Contacts.CustomerID

ContractorsDetails table - related to the Contacts table through contact.ID <===== this is really where I'm not 100% sure.

All customers have associated contacts BUT some of the contacts MAY be contractors at the same customer as well. As it's not a 100% of the time fact that ALL contacts will also be contractors, and not all contractors are tied to a customer the way I've approached it so far is to

a) If a contact is ONLY a contact (and not a contractor) then there is no entry for them in the ContractorsDetails table but there is a link between them and the CustomerID they work for.

b) If a contact IS also a contractor then the additional details I need to store on them because they're contractors are stored in the ContractorsDetails table (linked against their contact.ID which in turn links to the Customer.ID)

c) if a contact is only a contractor (i.e. don't have a relationship with a Customer) then they have details in the contact table and the ContractorsDetails table with a link from the contact.ID to a Customer.ID that's pre-defined JUST for contractors

I was tempted to separate out the contractors completely but due to the fact that they may also be contacts (which have other things like call-records etc associated with them) I thought this was the best way to procede.

If this is glaringly obvious "no-no" or one which will potentially cause headaches later-on?

Any advice would really be appreciated!

Cheers Martin

pritcham wrote:

Hi

I'm currently testing LLBLGen for an app I'm re-writing in .Net (from VB6) as I want to separate out the DAL which was previously wrapped up amongst the GUI layer.

As I'm pretty much starting from scratch I'm having to adapt my thinking/approach on all aspects of the development and so am re-looking at the DB design as well incase any adjustments need to happen here (which won't be too much of a problem at this stage).

One of the things that's been highlighted so far with my initial run-throughs with llblgen is the relationships between tables and I suppose I'm just after a second opinion to reassure me (or otherwise!) that my current approach is the best approach (or not as the case may be!).

Anyway, the basic design so far is...

Customer table (with all the obvious details stored here)

Contacts table (again, all obvious info stored here) - related to Customer table through Contacts.CustomerID

ContractorsDetails table - related to the Contacts table through contact.ID <===== this is really where I'm not 100% sure.

All customers have associated contacts BUT some of the contacts MAY be contractors at the same customer as well. As it's not a 100% of the time fact that ALL contacts will also be contractors, and not all contractors are tied to a customer the way I've approached it so far is to

a) If a contact is ONLY a contact (and not a contractor) then there is no entry for them in the ContractorsDetails table but there is a link between them and the CustomerID they work for.

b) If a contact IS also a contractor then the additional details I need to store on them because they're contractors are stored in the ContractorsDetails table (linked against their contact.ID which in turn links to the Customer.ID)

c) if a contact is only a contractor (i.e. don't have a relationship with a Customer) then they have details in the contact table and the ContractorsDetails table with a link from the contact.ID to a Customer.ID that's pre-defined JUST for contractors

Having a bogus customer is not a right solution.

You're re-using fields in the contact table, for contractors, but what I have a problem with is that you have a relation contact - customer which is mandatory, though when the data in contact is for a contractor, the relation contact - customer is not mandatory anymore.

Though when a contact is a contractor, it again is

I was tempted to separate out the contractors completely but due to the fact that they may also be contacts (which have other things like call-records etc associated with them) I thought this was the best way to procede.

If this is glaringly obvious "no-no" or one which will potentially cause headaches later-on?

I assume contractors always have a 'contact' part, correct? So that contact part is either associated with a customer (not NULL) or not (NULL).

You can objectify the relation contact-customer though: ContactCustomer ContactID* | CustomerID*| Role where ContactID, CustomerID form the PK and a UC constraint is defined on ContactID.

Role then explains the relation of contact with Customer: as contact or as contractor. If the data in contact is solely to store contractors, no row is added to ContactCustomer.