Re: REFERENCES to foreign tables

On May 21, 2005, at 9:08 PM, D.C. wrote:
> I'm trying to build some more complexity into the db that I
> mentioned recently. If in the 'clients' table, I have
>
> client_id int NOT NULL PRIMARY KEY CHECK (client_id > 0)
> name varchar(50) NOT NULL
> [...]
>
> ... and in the 'sales' table, I have ...
>
> sale_id int NOT NULL PRIMARY KEY CHECK (sale_id > 0)
> sold_to INT NOT NULL REFERENCES clients (client_id)
> [...]
>
> So when a sale is entered, postgreSQL keeps a record of who it was
> sold to. Now what happens if I want to delete the client who
> bought the item in question ? I'm going to have a 'sales' table
> that references a buyer who no longer exists (in db terms,
> naturally). I wondered aloud (talking to the wall, sort of) if I
> could reference clients.client_id to sales.sale_id, but wouldn't
> that stop my adding a client who hasn't yet made a purchase ?

A couple of thoughts. I assume you want to keep the sales around even
after you've deleted the client. If this is the case, then I think
you have two options. One is to add a "deleted" boolean column to
your clients table (probably with a default value of false to make
inserts a bit more convenient). You won't actually delete any of the
clients, but rather update deleted to true when you no longer want
them around.

The other option is to remove the sold_to column from the sales
table, creating a sales_clients join table instead which references
both the clients and sales tables. Then, when you delete a client,
the corresponding rows from the sales_clients table will be deleted
as well (depending on your cascade settings). Your sales will still
be in the sales table, but there will no longer be any client
associated with the sale. Depending on your model, you probably will
want to put a unique(sales_id) constraint on the sales_clients table
to restrict the number of clients per sale to 1, which I assume is
consistent with your current sales table.

I haven't built a system like this, but this is how I'd go about it.
I'd be interested in hearing others thoughts as well.

I'm not familiar with your earlier post concerning this db, so
perhaps this isn't applicable, but a few points:
1. "name" is a reserved SQL key word and may cause unexpected
results. I'd change the clients.name attribute to something like
clients.client_name.
2. I'd also put a unique(client_name) constraint on the clients table
to guarantee I don't duplicate client names. That can get a little
confusing :)
3. Any reason not to use serial types for your client_id and sale_id
attributes? They're very convenient, and are guaranteed unique. Ids
such as these are often kept hidden from the user. If you have
another id you need to enter, for example one generated by another
system, you might want to keep that as an added column. This allows
you a bit more flexibility in case of future changes.

Just my ¥2. Take it or leave it :)

Does this help?

Michael Glaesemann
grzm myrealbox com