I don't know what database you're using, so this suggestion may not be useful to you. As I see it, mixing your clients data together into a unified set of tables might be a big can of worms. How about giving each client their own database with identical table structures? That way, you can partition your data with the login process. You can use the same code without having to put in a customer (pre|suf)fix on your table names.

If you have some tables with the same data for all customers, you can split that out into another database and access them directly via qualified references or database linking (depending on which database you use).

Regarding the custom data elements, I'd put those in appendix tables so you can keep the columns used by all customers separate from the columns used by some other customers. Something like:

create table customers (
   customer_id int identity primary key,
   name varchar(64)
)
create table customer_extras (
   customer_id int primery key foreign key customers(customer_id),
   shoe_size int
)

[download]

One advantage of the multiple databases strategy is you can easily move databases to multiple servers should the need arise for particular security and/or performance needs.

--roboticus

In reply to Re: OT: Data Warehousing Strategies by roboticus
in thread OT: Data Warehousing Strategies by Booger

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.