design question

[nekrecart]

Hello,

I'm struggling with a design question.

In our database we have a lot of 'one-person-contact' data. We work with clients, distributers, suppliers, manufactures, etc...

I was wondering if it was a good idea to put all those in one table (entities) and give them a kind of 'role'. This way one person is entered one time in the database and can be a supplier and manufacture at the same time.

Ex.
Entities
#ID #Name
1 AClientName
2 AClientName02
3 AManufactorName
4 ADistributorName

Roles
#ID #description
1 client
2 distributor
3 suppliers
4 manufactor
6 driver


EnitiesRoles.
#ID #entID #RoleID
1 1 1
2 2 1
3 3 4
4 3 1
5 4 2
6 1 6


The nice thing is it's all in one table.
What will happen if you have complex SQL statement like
"Give me the suppliername and distributorname of product X which is delivered with client Y?"

So you have to access the same table 3 times (clientname, suppliername, distributorname). Does this has implications on performance, cost,..?

Is it a good design decision or am I fighting all the design laws?

 

[MDXer]

In response to the portion


So you have to access the same table 3 times (clientname, suppliername, distributorname). Does this has implications on performance, cost,..?


Not if your star schema is optomized and it is a MOLAP cube so it does not join out to the dimenson table. If your star schema optimizes the cube build with a simple

 select column(s) from table

I have worked a cube in the past that had a single product dimension table but the cube contained 2 product dimension that allowed comparison to show customers switching products. Simliar in concept to what you want to do.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck