Database question

[Tom]

I have this idea for a project, that is going to store a lot of different information from its users, and it would be nice if it was easily scannable (ie: not horrendously slow to generate reports).

The data isn't all directly related, and adding more types of data as time goes on will happen. So what I'm wondering is what is the best way to store it? Explicit hierarchy of tables, or a more dynamic (but probably slower) meta index type setup where specific data may not even have its own table, or maybe even another way i haven't mentioned (nosql maybe?).

A second, related question, is I'm going to want to be able to track, and correlate the data to pick out patterns or trends. This is not something I know much about at all, and I figure at least a couple of RWers know a bit about this, and could give me some hints.

[Mr. Analog]

If you know you need to do fast reads create tables that represent those reports and load them on CRUD operations. It may slow down write activity but it will vastly speed up reports if all you are doing when a report is requested is a flat select

[Tom]

It's sort of odd, since the main user facing interface is an app, its not going to want to bog down on reads or writes too much. The real question is how much is too much. I've heard recently that a lot of users don't really care about some lag from apps, and it shows in facebook's app lately. lol. But then my app will actually want to show some of the reports to the users themselves when asked, so clearly they aren't something I can/should do on request. I will have to think about that though, store the data needed for the reports in a sane structure.

[Mr. Analog]

No I'm saying choose where you want performance. It's either read OR write. Either has consequences on the other

[Mr. Analog]

The flatter the structure you read the faster it will return

[Thorin]

Oh boy, reporting and database structures!

My first question is are you sure the data is all that different?  Will your app use one UI for the users to input all this data?  If so, isn't it really all the same, then?

[Tom]

No I'm saying choose where you want performance. It's either read OR write. Either has consequences on the other

I understand that.

I was thinking a change in the main data could trigger a background job, potentially on a mirror instance of the database, to build the flat report tables. that's probably not something I'll start with, but i think its a valid option. I think it'll depend on just how long it takes to build up the data.

Oh boy, reporting and database structures!

My first question is are you sure the data is all that different?  Will your app use one UI for the users to input all this data?  If so, isn't it really all the same, then?

Nope, much different ui for most types of data.