mardi 14 juin 2016

fact table referencing another fact table

i'm very new to database design and wanted to ask if the following scenario is possible. I'm currently trying to design a db that contain an entity called Asset. Asset entity would have different types of assets such as laptop, desktop, software, etc. Beside the Asset table, there also would be assetInfo table and corresponding asset type tables (ex. Desktop, Laptop, Software, etc). Asset Type Tables contain info about that asset (id, model, date purchased, etc). Asset table contains the info about which asset it is (is it a laptop, is it a desktop, is it a software?) and assetInfo table contains the information of the current-user of the asset (which employer under what department and what building, room is it placed).

In order to implement this, I came up with two ideas.

The first one was making it Asset entity a parent class and types of assets a sub-class and have a x number of disjoint relationship between asset and type of assets. something tells me this would bring a lot of complexity as the number of type of assets increase.

Another option is having a star schema. With asset being a fact table, types of asset could be a dimension table. The only problem with this is that when I have another table called assetInfo which is supposedly show who has which asset at certain location, it would require to have all the primary keys of asset entity (correct me if im wrong with this). So my question is: is it possible to have a fact table referencing to another fact table?

An example would be:

Asset (Fact table)

PK: SoftwareID PK: DesktopID PK: LaptopID FK: SoftwareID FK: DesktopID FK: LaptopID

Software

PK: SoftwareID

Laptop

PK: LaptopID

Desktop

PK: DesktopID

Employer

PK: EmployerID

Department

PK: DepartmentID

Building

PK: BuildingID

Room

PK: RoomID

assetInfo

PK: assetID PK: EmployerID PK: DepartmentID PK: BuildingID PK: RoomID

Aucun commentaire:

Enregistrer un commentaire