Re: Design question: Scalability and tens of thousands of

I have thought of this as a possible solution. Unfortunately I want to do
reporting (sum, avg, ect.) queries on this data (for each user) and I can't
imagine that being feasible with casting that many columns.

Zack

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of george young
Sent: Tuesday, November 08, 2005 6:15 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Design question: Scalability and tens of thousands of

On Thu, 3 Nov 2005 09:58:29 -0800
"zackchandler" <zackchandler(at)hotmail(dot)com> threw this fish to the penguins:
> I'm designing an application that will allow users to create custom
objects
> on the application level. A custom object can have zero or more
attributes.
> Attributes can be one of 5-10 types (ex. String, Number, List, Date, Time,
> Currency, etc...). This will allow users to track anything exactly as they
> want. My first thought on how to structure this is to make a custom table
> in the db for each custom object. The attributes would map to fields and
> everything would work just dandy.
>
> The problem is I am very concerned about scalability with having a
different
> table created for each custom object. I want to design to site to handle
> tens of thousands of users. If each user has 3-5 custom objects the
> database would have to handle tens of thousands of tables.
>
> So it would appear that this is a broken solution and will not scale. Has
> anyone designed a similar system or have ideas to share?

This is a sticky problem. My app is a bit similar. Trying something
like your solution, I found that zillions of tables, constantly created
and destroyed by users, to be terrible to manage. Now I use one table
that defines objects' attributes, e.g. something like:

create table fields(owner text, obj text, name text, type text, seq int)
create table objs(owner text, name text)
create table values(owner text, obj text, name text, val text)

That is, the values are stored in text type, not the native type.
Yes, this takes a performance hit for conversion of values, but the
simplicity of schema really wins for me. I suggest you seriously consider
it unless you need blinding performance in all 20,000 applications...

-- George Young
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match