need opinions

I am developing a web application that will host many companies' data in one
database. And there will be the possibility that the company may decide to
take their data at some point and host it themselves.
So my question relates to the table structure and relationships. If a
company decides to take their data, I want it to be as easy as pulling all
data from all tables where Company_id = x. That said, let's just assume I'm
going to have the Company_Id in all tables or most. Do I create a separate
primary key field that's unique across all companies or unique within the
company_id ? I like the idea of all companies starting at 1 for their
primary key IDs, but this also brings more complexity and inefficiencies to
querying the database. Plus it means defining a two field unique index per
table and having to base all relationships between tables on two fields.
The joins are more complicated.
I'm leaning toward using one primary key field across all companies to link
tables, but still having the Company_id in all tables just to be able to
extract the data easily if I ever need to. The Primary key field would not
be an Auto ID, as this would pose problems when migrating existing data to a
new database.
Or do I just use a different database per customer. I expect having 1000 or
more companies, so I don't think separate databases will fly.
Looking for your opinion and getting ideas about how things are being done
elsewhere.
thanks,
BillBill
CREATE TABLE Companies
(
company_id INT NOT NULL PRIMARY KEY,
company_description VARCHAR(50) NOT NULL
.....
.....
)
CREATE TABLE Companies_Data
(
row_id INT NOT NULL PRIMARY KEY,
company_id INT NOT NULL REFERENCES Companies (company_id),
comp_activities VARCHAR(...) or INTEGER ( Depends on your business
requierements)
customer_id.....
.....
)
CREATE TABLE Customers
(
custoemid INT NOT NULL PRIMARY KEY,
custname VRACHAR(50) NOT NULL,
....
)
Look , the company may have more than one actvities , right? Consider
crerating an UNIQUE CONSTRAINT on these fields
> Or do I just use a different database per customer. I expect having 1000
> or more companies, so I don't think separate databases will fly.
Well, I have seen some systems that customers hosted their databases (with
costomer info) and just connected to the main server to make operations as
well as the main server has all info about customers. It is really depends
on your business requirements.
"Bill H" <bill@.somewhere.com> wrote in message
news:OwWFpNXTGHA.5900@.tk2msftngp13.phx.gbl...
>I am developing a web application that will host many companies' data in
>one database. And there will be the possibility that the company may
>decide to take their data at some point and host it themselves.
> So my question relates to the table structure and relationships. If a
> company decides to take their data, I want it to be as easy as pulling all
> data from all tables where Company_id = x. That said, let's just assume
> I'm going to have the Company_Id in all tables or most. Do I create a
> separate primary key field that's unique across all companies or unique
> within the company_id ? I like the idea of all companies starting at 1
> for their primary key IDs, but this also brings more complexity and
> inefficiencies to querying the database. Plus it means defining a two
> field unique index per table and having to base all relationships between
> tables on two fields. The joins are more complicated.
> I'm leaning toward using one primary key field across all companies to
> link tables, but still having the Company_id in all tables just to be able
> to extract the data easily if I ever need to. The Primary key field would
> not be an Auto ID, as this would pose problems when migrating existing
> data to a new database.
> Or do I just use a different database per customer. I expect having 1000
> or more companies, so I don't think separate databases will fly.
>
> Looking for your opinion and getting ideas about how things are being done
> elsewhere.
> thanks,
> Bill
>