Is this a Proper Data Model?

[Anonymous]

I am rebuilding data model so wanted to get some feedback. If all tables have a key field present in them, data model below is possible. It seems to be working fine performance wise (its even better) but is it best practice to implement this type in your dashboard.

Thanks again.

[Clever_Anjos · Accepted solution]

Yes, it´s a nice model, you seems to have two dimension tables (Dept & Vendor), two fact tables (Trans & Inv).

You don´t have more than one hop between your tables.

[#3]

My concern would be using ProductID as the only key.

What would happen if you had a product associated with multiple vendors or departments?

[Anonymous]

Technically it looks fine.

Logically...  it is somewhat strange.  Transaction table has Vendor and Department data, so it's not clear why it is linked to the Department and Vendor tables by ProductID.  I'd expect a little different structure:

Transaction in the center as a fact table.

Dimension tables Vendor, Department, and Product.

But it is a rather wild guess because I don't know your data nor the requirements.

[Anonymous]

Clark, your concern is valid. This is a hypothetical example though. I was just trying to evaluate the credibility of the structure because it does not resemble typical star schema which has a fact table in the center, from which dimension tables radiate. That aside, in a company where I used to work, we used distinct product ID if we were buying the same exact thing from a diff vendor; meaning the product wud get a new distinct productID if buying from a diff vendor. As per 1 product belonging to more than 1 department, our products had only 1 dept assigned to them. But depending on the nature of Business you are in, that could be an issue I guess.

[Anonymous]

Michael,

you are absolutely right. This was just a hypothetical example where I was trying to see if such a data model (looks wise) is acceptable and to which I got a positive reply. I will go ahead and implement this model in my application.

Many Thanks.