database - How combine user device data and fact table design? -

it needed store following information:

1. user device data device id, device brand, screan size, type (pc, tablet, mobile).
2. actual facts, odrder id, total cost, goods count, goods ids etc.

how put user device data - fact or dimension table?

if put fact table, have lot of duplicates, e.g. each row have cotained detailed information device, same 1 user.

if put dimesion table, have update dimension table - add new device or update older.

important note here analytical system designing not analyze user behaviour, see data not gather. there can situation when different application versions provide different "fullness" of information - 1st version gather 50% of user device, 2nd - 75%, 3rd - 99%. have update (not insert) data in devices table.

you need @ least 2 dimensions i.e. user , device snowflake schema. user dimension should reference device table device id.

user keep list of users. use foreign key reference device table(on device id). might go scd type handle data changes. better in comparison if device fields on single table.

device table hold different types of devices , attributes.

the fact need reference (userid, deviceid) combination user table.