in reply to OT: Data Warehousing Strategies

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

Replies are listed 'Best First'.
Re^2: OT: Data Warehousing Strategies
by Booger (Pilgrim) on Aug 27, 2006 at 17:31 UTC
    We're using MySQL at the moment. I would personally prefer using PostgreSQL but the other developers aren't familiar with it and depend too highly on phpMyAdmin.

    The new version of the application will run on MySQL 5 so I'll have all of its features at my disposal.

    I'm a little hesitant to split into several databases (although you provide good reasons for doing so) but only because I don't know what the ramifications are when it comes to doing a select across multiple databases (something I've very rarely done). We would have to do this because we have to combine customer data quite frequently when presenting it to the end user.

    Thanks for your input!

[reply]
      I'm not terribly familiar with MySQL. Most of my experience is with Sybase and MS SQL Server (which are very closely related). In these, you simply prefix the table name with the database name and owner to access a table in a different database. (Assuming, of course, that the login has permissions on both databases.)

      Suppose for example that you have two databases, common and cust1. The common table has table states which contains the full name of each state (full_name), keyed by its abbreviation (ST). The cust1 database has table customers which has the customer number (Cust_ID) and state (ST). If you're logged into cust1 and want to count all the customers by state and list the state name, you'd do something like this:

      select common.dbo.states.full_name, count(customers.*)
from customers
join common.dbo.states on customers.ST = common.dbo.states.ST
group by common.dbo.states.full_name
order by common.dbo.states.full_name

      [download]

      So it's a bit verbose. But there's another trick you can use in these two databases: You can hide the table in the remote database behind a view so it looks just like a table in the current database. Something like this:

      create view states
as select ST, full_name from common.dbo.states

      [download]
      Now you can perform the previous operation more naturally:

      select states.full_name, count(customers.*)
from customers
join states on customers.ST = states.ST
group by states.full_name
order by states.full_name

      [download]

      As I said earlier, I'm not very familiar with MySQL (not having used it since about v3.1), but I'd imagine that it offers similar capabilities. Hopefully a knowledgeable MySQL or PostgreSQL user will chime in with similar operations, or improved suggestions.

      --roboticus

[reply]
[d/l]
[select]
        What you're describing sounds right and AFAIK it's the same in MySQL as MS SQL Server & Sybase (both of which I've used as well, albeit in somewhat limited capacities). My father-in-law is a DBA for a midsized company that and does fancy DB stuff like OLAP & multi-dimensional databases. I'm going to see him this weekend at the Jersey shore and may bend his ear about it if I need to (my wife & I live in a different country so we don't get to see them very often).

        Thanks again roboticus!

[reply]

In Section Seekers of Perl Wisdom