Skip navigation links

MySQL Forums :: Data Warehouse :: Dimensional model, user ids and number of tables


Advanced Search

Dimensional model, user ids and number of tables
Posted by: Mads Jensen ()
Date: June 24, 2009 01:07PM

Hello

I'm currently working on the dimensional model design for a redesign of a service. I've been searching far and wide for an answer to the following question, without luck.

So my problem isn't the dimensional model itself, the problem exists due to the nature of the project. It will be a 'software as a service' type project where users can sign up and use the service for free or through payment.

This means that most data (all fact tables and most dimensions) will have a user id associated with each row.

So my concerns are about performance and tables sizes.

As i see it, i have 3 options on how to place the tables in my database (InnoDB):
Every option would be able to use partitioning as needed.

1.
----
One or a few very large fact tables and dimension for all users(>50+ millions of rows).
As of now it would contain data for 5000+ users with anywhere from 10.000 - 100.000 rows (facts tables and dimensions) per user.

Concerns:
Performance due to joins (must be joined not only be keys, but also by user_id = '111'.
Unmanageable database size

---

2.
---
'Clusters' of users sharing facts tables and dimensions. Maybe 3-500 users for each dimensional model.
This would give me more manageable tables sizes, but i would still have to save the user_id with most rows.
Would give me some hundred tables, some with redundant data.
---

3.
---
One dimensional model per user. Could be some 'static' dimensions that more could be used across multiple user dimensions.
I would then not have to save the user_id in the table and thereby increase performance using joins.
But i would also have thousands of tables in the database (with 5000 users probably at least 20.000 tables).
---

Any adwise on which way to go with this?

Options: ReplyQuote


Subject Views Written By Posted
Dimensional model, user ids and number of tables 7679 Mads Jensen 06/24/2009 01:07PM
Re: Dimensional model, user ids and number of tables 3697 Rick James 06/25/2009 09:15PM
Re: Dimensional model, user ids and number of tables 3507 Mads Jensen 06/26/2009 07:17AM
Re: Dimensional model, user ids and number of tables 3532 Rick James 06/27/2009 12:24AM
Re: Dimensional model, user ids and number of tables 3459 Mads Jensen 06/30/2009 12:48PM
Re: Dimensional model, user ids and number of tables 3527 Rick James 06/30/2009 10:00PM
Re: Dimensional model, user ids and number of tables 4133 Mads Jensen 07/03/2009 04:20AM
Re: Dimensional model, user ids and number of tables 3376 Rick James 07/03/2009 02:21PM
Re: Dimensional model, user ids and number of tables 3425 Mads Jensen 07/04/2009 03:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.