I have created Company, Manufacturer, Distributor and Courier tables for use as supertype and subtypes of companies. This solved the part of my problem where I wanted to create type specific companies. Now I am running into a second problem with this as I want to have one company, company x, to be both a Manufacturer and a Distributor. So what I was thinking when designing this was that I would insert company x as a manufacturer which creates its unique id (identity field) in the table Company and inserts this id into the Manufacturer table. I would then have to manually add this id to the Distributor table to make this same company/manufacturer also a distributor. Now as I think about this more it feels like bad design or that I am missing something. The only thing that I can think to do is add a table called CompanyRole to the system and create an association between Company and CompanyRole. Then I would be using two different methods of keeping track of what a company is. This still feels wrong to me.

Any idea’s would be greatly appreciated.

Regards,

Aaron

That's a commonly used design and nothing wrong with it at all (if I'm understanding you correctly).

If this is a normal way of doing this would it then be correct to create a admin screen that would allow a user to search for a manufacturer in which they want to make a distributor and fill in the distributor specific attributes?

Yeah, and then it's just a matter of creating that record in the distributor table. Same PK value.

tprohas wrote:

I have created Company, Manufacturer, Distributor and Courier tables for use as supertype and subtypes of companies. This solved the part of my problem where I wanted to create type specific companies. Now I am running into a second problem with this as I want to have one company, company x, to be both a Manufacturer and a Distributor. So what I was thinking when designing this was that I would insert company x as a manufacturer which creates its unique id (identity field) in the table Company and inserts this id into the Manufacturer table. I would then have to manually add this id to the Distributor table to make this same company/manufacturer also a distributor. Now as I think about this more it feels like bad design or that I am missing something. The only thing that I can think to do is add a table called CompanyRole to the system and create an association between Company and CompanyRole. Then I would be using two different methods of keeping track of what a company is. This still feels wrong to me.

As your company has 2 different supertypes, it's better of with the decorator pattern as it is called if I recall correctly: you branch out the specifics for the type(s) the object can have, as you can't use a specific type for the object itself to do that. (you could, with interfaces, but in a db you don't have that).

So, in your case: you have company, manufacturer and distributor and courier . In company you store the company details, it gets its PK etc. If the company is a manufacturer, you store manufacturer details in the manufacturer table. It has an FK to company. You do the same in distributor. When fetching a company, you then always fetch manufacturer and distributor as well. When distributor is empty, the company isn't a distributor. When manufacturer is empty, the company isn't a manufacturer. Same with courier.

In your company entity, you can add some code which checks internally which data is available and returns what the role of the company is.

This is the easiest way to fetch the data. you can also store it as roles, though you then need a new table, companyroles, which stores the pk of company, the pk of manufacturer or distributor and a roleid. the roleid is then interpreted and used to select to which table to join: manufacturer or distributor. this is cumbersome and slower.

As Jim said, your approach is the most common. Don't forget to always fetch the subtypes when fetching the supertypes

ps: multiple-type inheritance isn't supported in 1.0.2005.1, I'm still undecided to support it in 2.0, as it can be done with interfaces, though it has complications with fetching data and storing data.

Otis wrote:

tprohas wrote:

I have created Company, Manufacturer, Distributor and Courier tables for use as supertype and subtypes of companies. This solved the part of my problem where I wanted to create type specific companies. Now I am running into a second problem with this as I want to have one company, company x, to be both a Manufacturer and a Distributor. So what I was thinking when designing this was that I would insert company x as a manufacturer which creates its unique id (identity field) in the table Company and inserts this id into the Manufacturer table. I would then have to manually add this id to the Distributor table to make this same company/manufacturer also a distributor. Now as I think about this more it feels like bad design or that I am missing something. The only thing that I can think to do is add a table called CompanyRole to the system and create an association between Company and CompanyRole. Then I would be using two different methods of keeping track of what a company is. This still feels wrong to me.

As your company has 2 different supertypes, it's better of with the decorator pattern as it is called if I recall correctly: you branch out the specifics for the type(s) the object can have, as you can't use a specific type for the object itself to do that. (you could, with interfaces, but in a db you don't have that).

So, in your case: you have company, manufacturer and distributor and courier . In company you store the company details, it gets its PK etc. If the company is a manufacturer, you store manufacturer details in the manufacturer table. It has an FK to company. You do the same in distributor. When fetching a company, you then always fetch manufacturer and distributor as well. When distributor is empty, the company isn't a distributor. When manufacturer is empty, the company isn't a manufacturer. Same with courier.

In your company entity, you can add some code which checks internally which data is available and returns what the role of the company is.

This is the easiest way to fetch the data. you can also store it as roles, though you then need a new table, companyroles, which stores the pk of company, the pk of manufacturer or distributor and a roleid. the roleid is then interpreted and used to select to which table to join: manufacturer or distributor. this is cumbersome and slower.

As Jim said, your approach is the most common. Don't forget to always fetch the subtypes when fetching the supertypes

ps: multiple-type inheritance isn't supported in 1.0.2005.1, I'm still undecided to support it in 2.0, as it can be done with interfaces, though it has complications with fetching data and storing data.

I think I understand how to do this, but am still unsure if it solves all of my problem. I still don't like the idea that I have to manually add the company pk to the distributor fpk when say the company is already a manufacturer. Is this a matter of creating the "CreateCompany" UI so that when a company is created the user can choose what types of companies they want. Also, if a user wants to create a distributor out of a manufacturer later the would have to be this functionality on the "EditCompany" UI.

I am having a similer problem with the scenario of Person, Customer, Employee. How do you deal with creating a Person/Employee and then this Employee becomes a Customer. Normally I would think that new Person/Customer records would be created and now I have the same person in the database twice. Once as the Employee and then again for the Customer. How do you deal with this type of problem. Is this the same problem as the Company.

If an employee becomes a customer as well, the 'person' data doesn't change nor is it copied. The employee record points to the person record via an FK, and so does the customer record. You should see the customer record and the employee record as 'extensions' of the complete data of an entity. In a relational model in NIAM for example you probably wouldn't see the attributes as a different entity.

So, you have a Person object in memory, you set its Employee field to an Employee instance and its Customer field to a Customer instance. Then save Person, and everything is synced for you.

What I am having a problem with is that the Employee and Customer are created at different times.

I am currently using an identity field as the pk which is not very good at enforcing uniqueness of a person entity. What I have done to help this is created a PrimaryEmail field in the Person table which is a unique constraint. Now when a new Person/PersonType is created the email is the only thing I can use to try and enforce this uniqueness. I think the scenario I would have to follow would be something like the sequence below.

Create Person/PersonType. Check if email exists. If email does not exist then create new Person/PersonType. If email exists it means that this person is already in the system as another type. Add pk from Person table to PersonType table.

This could also be written like...

Create Person/Customer. Check if email exists. If email does not exist then create new Person/Customer. If email exists add pk from Person table to Customer table.

or...

Create Person/Employee. Check if email exists. If email does not exist then create new Person/Employee. If email exists add pk from Person table to Employee table.

Is this right?

tprohas wrote:

What I am having a problem with is that the Employee and Customer are created at different times.

I don't think that's a problem. The only problem semantically is that as soon as an instance can be multiple types which all inherit from the same supertype, you can't use inheritance, at least not in a single-inheritance invironment as .NET and when interfaces aren't supported for dataaccess.

I am currently using an identity field as the pk which is not very good at enforcing uniqueness of a person entity.

Why would an identity field not be unique? Isn't it the PK?

What I have done to help this is created a PrimaryEmail field in the Person table which is a unique constraint. Now when a new Person/PersonType is created the email is the only thing I can use to try and enforce this uniqueness.

Please don't do this. You're now adding a second PK which is a surrogate key, which is a fix for apparently a non-unique PK (?) so I then would throw away the real PK and use this surrogate instead. Though I think the identity field is just fine for uniqueness...

I think the scenario I would have to follow would be something like the sequence below.

Create Person/PersonType. Check if email exists. If email does not exist then create new Person/PersonType. If email exists it means that this person is already in the system as another type. Add pk from Person table to PersonType table.

This could also be written like...

Create Person/Customer. Check if email exists. If email does not exist then create new Person/Customer. If email exists add pk from Person table to Customer table.

Isn't this just: myPerson.Customer = new CustomerEntity(); myPerson.Customer.SomeProperty = someValue; // save myPerson recursively.

Maybe I can explain this using code.

Lets say that the create customer code looks like this.

PersonEntity person = new PersonEntity();
person.Employee = new EmployeeEntity();
person.FirstName = "first name";
person.LastName = "last name";
person.PrimaryEmail = "username@domain.com";
person.Employee.Title = "title";
person.Employee.HiredDate = DateTime.Now;
ServiceFactory.GetPersistanceManager().SaveEntity(person);

Now two months later this new employee wants to make a purchase which would make this employee a customer. Don't I have to make the code below check to see if this person already exists as an employee? Otherwise this code is going to create a new record for the person/customer. If I have first name, last name, and email as the required fields for any person and I am using a identity column as the primary key how do I make this work?

PersonEntity person = new PersonEntity();
person.Customer = new CustomerEntity();
person.FirstName = "first name";
person.LastName = "last name";
person.PrimaryEmail = "username@domain.com";
person.Customer.CustomerLogin.Username = person.PrimaryEmail;
person.Customer.CustomerLogin.Password = "password";
ServiceFactory.GetPersistanceManager().SaveEntity(person);

Ah ok I see your point.

If you want to do this automatically, then indeed you have to use the put in info to find back a unique person so you can decide if the person is already in the db.

On the other hand, it might be this automatic system makes a mistake, either by a faulty filled in email address or by having 2 results, because of inproper chosen data. Isn't it better to use the information you know about the person doing hte purchase? Like: the person is logged in as an employee?

For example, the one reason you would normalize out the person fields is to keep redundancy low. The problem is though that redundancy isn't always a bad thing. For example, the CUSTOMER registers with an address and the person is for example female and not married. The person is also an employee. Logging the customer credentials separately can have the benefit that you can go back in time later on and have the exact data of that date, because what happens if the person marries and changes her name? Or leaves the company? Do you want to leak that kind of future information into your history of customer information? I'm not sure.

It's a tough call, what's best. I think it also depends on details like I mentioned above, which could lead to the decision to just keep 2 tables with employee data and with customer data, and if an employee becomes a customer too, big deal. One thing you can do then is to reference a customer entity from the employee entity IF they're customer. (or multiple in an intermediate table) and handle that in the gui which is used solely for employees.

Otis,

Thank you for the time, I think I am going to just leave the Customer and Employee data seperate for the moment. It seems easier for now and I'll deal with figuring out what employees are also customers later. I did like you idea for doing this with an intermediate table for employees only.

Enjoy your day!

Glad I could help out!