> we have on table with 80 millions of reccords (growing). each records is
> assigned to a defined Contact ID. Only one contact ID can access read
> his record.
> i thing it's a pretty classical scenario ...
> we have 2 option :
>
> 1/ keep all the reccord in one unique table and add a column Contact_ID
> and filter all queries on this colum
> Good: easy, only one table to manage
> Bad: very big table and very heavy index (all index (mostly all) must
> have the field Contact_ID to be efficient)
>
> 2/ create for each contact his dedicated table xxx_ContactID.
> Good: performance because all table are little, index are little, etc.
> also posibility to split in several server to win in performance. (but i
> thing it's not really possible (i thing) by one trigger to update the
> table in another database)
> Bad: management of creating/destroying table by trigger is not really
> supported under firebird. and what happen when more contact than
> firebird max limit of allowed # of table ?
>
> So what do you advise to do in such case ?

- How many distinct contact ids do you expect
- How does your query pattern look like?

Because of latter, is contact id the only criteria for filtering rows or
are there more (e.g. date/time, product etc ... information)?

You always can work with an archive and "most recent" table (maintained
by triggers), in case of performance slow down due to increasing data
volume. This, possibly is in relationship with your query pattern as well.


--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!