Re: Primary Key Dilemma

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 07/18/04 

Date: Sun, 18 Jul 2004 14:58:33 +0530

I think keeping a Customers table and link
other tables with these table with the CustomerId as the foreign key
will solve your problem. 

-- 
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jeremy" <A@B.com> wrote in message
news:%23RQ1VMHbEHA.1548@TK2MSFTNGP12.phx.gbl...
> I'm designing an application that will be sold to multiple customers, each
> of which is a small business. The database behind this application will be
> accessed via the Internet, and will be shared by *all* customers. Each
> business is permitted to access only its own data. For a variety of
reasons,
> we don't want to maintain a separate physical database per customer. This
> creates a scenario in which we would have one physical database that
> implements multiple logical databases (one per customer).  This creates a
> unique (to me anyway) problem when it comes to defining primary keys.
Think
> of any expected table in this database ('products', for example). It would
> not be enough to implement an auto incrementing integer column as the
> primary key because we need to also include the business_id (the unique
> identifier of the small business that owns the product described in the
> row). It just doesn't seem right to me to have an auto inc column AND a
> separate business_id column comprising the primary key.
>
> I'd appreciate any suggestions on determining the primary key to use in
this
> database.
>
> Please note that I'm not providing DDL because I think it's irrellevant at
> this point. It would be enough to consider my question as partaining to
the
> Northwind database... but instead of implementing the database for one
> company, we're implementing it for 20 companies that operate just like
> Northwind and have the exact same db requirements, and they will all share
> the same physical database. How would we go about modifying Northwind's
> primary keys so that we could associate any particular table's rows with
the
> owning business?
>
> Thank you.
>
>

Relevant Pages

  • RE: Store Added Value List Items
    ... (The Primary Key that the user updated added) ... Each user in this database needs to have access to all the same customers. ... is praticle to create a seperate one field table to store values for a lookup ...
    (microsoft.public.access.formscoding)
  • RE: Is Access the right app (newbie question)?
    ... Products, containing ProductID (primary key), Size, Model, etc. fields ... Customers, containing CustomerID, Address, etc. fields ... to record who makes/edits each quote) ... to the central database across the network. ...
    (microsoft.public.access.gettingstarted)
  • Re: Query to merge
    ... Thanks for that insite - I am sure that the additional customers (that would ... simply build your query to give you all records from the one, ... Say it was a customer database. ... customers with the same primary key. ...
    (microsoft.public.access.queries)
  • Re: Database Options - Any other settings I should adjust?
    ... I find business customers by word-of-mouth. ... to program or design a database application or how much work it involves, ...
    (comp.databases.ms-access)
  • need some SQL help
    ... I need to run a query which will return customers who match multiple order ... This works great on a small database but the ... PRIMARY KEY ...
    (comp.databases.theory)
Quantcast