Normal 0 21 false false false
MicrosoftInternetExplorer4 This is our first project using
PostgerSQL, where I have a problem I cant solve on a neat way (I assume PGSQL
should provide a nice solution...).
So we have an old xBase based program we are trying to port to PostgreSQL
while we should keep the original data structure especially the ID fields must
be kept as this IDs are already used in other systems.
The problem is with two table, one is storing the data of houses the other
the data of tenants in a given houses.
Something like this:
CREATE TABLE house (
house_id SERIAL,
.....
CONSTRAINT pk_house_id PRIMARY KEY(house_id)
) WITHOUT OIDS;
CREATE TABLE tenant (
tenant_id SERIAL,
house_id INTEGER REFERENCES house(house_id),
.....
CONSTRAINT pk_tenant_house_id PRIMARY KEY(tenant_id, house_id)
) WITHOUT OIDS;
For a given house I would like to start the numbering of tenants from 1. Each
house could have tenant_ID=1, obviously in this case the house_ID will differ.
The combination of tenant_ID and house_ID will be the unique identifier of each
tenant.
I'm just looking for the best solution to insert new rows into the tenant
database without worrying about keeping the above mentioned logic in mind.
Should I create a stored procedure to add a new tenant and this will calculate
the new house_id+tenant_id combination (only the house_id would be passed to
the stored procedure, the tenat_id will be calculated by the sp). In this case
how can I avoid that two concurrent user would try to add records in the same
time without getting an exception due to violating the pk_tenant_house_id
constraint? Or should I add a new field in the house table storing the last
issued tenant_id in the given house (max_tenant_id) which will be used while
adding a new record to tenant, and will be updated by a trigger on the tenant
table? Or am I thinking on a wrong way and there is a better mechanism provided
by PostgreSQL for this problem?
Any other suggestions would be greatly appreciated.
Thanks!
Zsolt
________________________________________________________<br>Csatlakozzon a
Bookline törzsvásárlói programjához, <br>és válogasson prémium és egyedi
akcióink
közül!<br>http://bookline.hu/news/news.action?id=2845&tabname=book&affiliate=frelinkar9773&utm_source=freemail_karakteres_level_alja&utm_medium=level_alja_karakteres_line_promo&utm_campaign=0910_line_promo
