Postgres Database lookup table-CodePudding

I have a database that in simple terms has Addresses, Buildings, Listings they are all linked together a building has an address, a listing has a building. The issue starts because there are multiple sources that the data comes from that use different id sequences and I have to keep track of the sources id for deleting and editing the data.

CREATE TABLE addresses
(
 "id"            serial NOT NULL,
);

CREATE TABLE buildings
(
 "id"              serial NOT NULL,
 address_id      serial NOT NULL,
);

CREATE TABLE listings
(
 "id"              serial NOT NULL,
 building_id     serial NOT NULL,
);

Example of the source ids

Source1: 24164454
Source2: 36uLTN8oq8JMV2Pg7fXaX0
Source3: 14524298

Obviously the way its setup now it couldn't handle source2's ids. The issue I am facing is I cant just set it to the sources id because for example source 1 and 3 could collide with their id structure.

My question then what is the best way to solve this? Lookup table? have source and source id as composite keys (this feels messy)

CodePudding user response：

It seem quite reasonable to have a column source_id and then an id column which will be type INT, the PRIMARY KEY and SERIAL.
If your id's are coming from different sources it is quite possible that you will have duplicates with the same number coming from different sources. For this reason I would advise against using it as an id and against making the column unique.
I had this problem in a company which used the suppliers references as their references which was all very simple until we realised that we had a lot of duplicates.