CODEWITHSUNDEEP
databasedatabase-designdimensional-modeling
s.brody
s.brody

Reputation: 185

How to adapt this simple relational model to a dimensional one?

I have this fairly basic relational model for storing bicycle models. It contains 2 tables:

    (modelID, modelName, brandName)
    (modelID,  type, color, wheelSize, suspension, frameMaterial, brakeManufacturer, gearType, gearModel, yearProduced)

I've been asked to adapt it to a dimensional model.

These would be the dimension tables :

    tbl_d_model(modelID, modelName)
    tbl_d_brand(brandID, brandName)
    tbl_d_color(colorID, color)
    tbl_d_type(typeID, type)
    tbl_d_wheel(wheelID, wheelSize)
    tbl_d_suspension(suspID, suspension)
    tbl_d_frameMat(frameID, frameMaterial)
    tbl_d_brakeMan(brakeId, brakeManufacturer)
    tbl_d_gear(gearID, gearType, gearModel)

And this would be the fact table:

    tbl_f_fact(modelID, brandID, colorID, typeID, wheelID, suspID, frameID, brakeID, gearID, yearProduced)

Is this an optimal way of doing it? Is there a better way?

Upvotes: 0

Views: 85

Answers (1)

SQL.injection
SQL.injection

Reputation: 2647

on Dimensional Modeling you use keys.

What do you need is a single dimension and not 10 dimensions and a fact table.

therefore

dimension_bycicle(key_bicicle, modelName, brandName, type, color, wheelSize, suspension, frameMaterial, brakeManufacturer, gearType, gearModel, yearProduced, modelId)

please not that modelId is your natural key (the operational system key and should not be used as the data warehouse key).

Upvotes: 1

Related Questions