0

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

Peter Chung
  • 1
  • 1
  • 1
  • 1
    `mysql` or `sql-server`? – Nikhil Vartak Jun 13 '16 at 20:30
  • I'm not sure if you understand "fact table" correctly - if I understand your data, `PK: DesktopID PK: LaptopID` would not work together (an "asset" will be either a laptor or a desktop, right?). Everything you listed is an asset on its own, right? (So a specific laptop is an asset?) I guess you can just use a table `asset(assetID, assettype, id, model, serial number, purchase date, ...)` (and fill only relevant columns). For specific stuff (e.g. volume licenses) you can add special tables. AssetInfo would work as you described. But without further details it's hard do be concrete. – Solarflare Jun 13 '16 at 21:15
  • @Solarflare yes and yes. If I were to use asset table as you suggested, what would "id" column be used for? (since we already have assetID) – Peter Chung Jun 14 '16 at 15:35
  • @Think2ceCode1ce its for mysql *edited* – Peter Chung Jun 14 '16 at 15:36
  • The `id` came from your example, you said "asset (id, model, date purchased, etc)". If you meant your primary key there, then that would already the `assetid`, you are right. You can basically just add a column for every piece of information you want to save about an asset. If you have a lot of asset types and thus a lot of columns, or if you plan to dynamically add asset types (e.g. by the user), there might be other and better options (generic columns, subtables, attribute tables), but that would depend on your specific requirements. With just 3 asset types, you should be fine for now. – Solarflare Jun 14 '16 at 16:51
  • @Solarflare that is much more efficient way of solving this problem, thanks a bunch! – Peter Chung Jun 15 '16 at 16:07

0 Answers0