NOTE: I am new for psql
How to add two different table column as reference in one field ( column ) in PostgreSQL
case:
- I my case, I have 3 table, "salary", "user_management", "driver_management"
- "user_management" and "driver_management" both table have column "user_id"
- "user_id" in these two separate tables should use only one column "salary" > "user_id" as reference.
below my psql query:
create table salary (
"id" SERIAL PRIMARY KEY,
"user_id" VARCHAR REFERENCES user_management(user_id) REFERENCES driver_management(user_id),
"basic_pay" INT,
"pay_group" VARCHAR,
"pay_method" Pay_Method,
"pay_cycle" Pay_Cycle,
"bank_name" VARCHAR,
"account_number" numeric(26),
"lmts" timestamp default current_timestamp
);
Top comments (5)
I'm not sure what question you are asking. From the perspective of 3NF your relationship appears to be correct: each Salary can be a user and/or a driver, each table holding different information
However...
Business logic would indicate that there should be at least one table 'above' Salary, probably Employee which should also have this user_id as a primary key (or at least a unique constraint)
You can join user and driver directly without going through salary or employee. Inner join to get records that exist in both, left join to get all records in one and records in the other if they exist or FULL OUTER JOIN to get all records in both but on a shared row if they match. If you need data from salaries employee then probably better to start there and Left Join to each if the other tables.
thanks for your idea! 😊
Sorry I didn't read your script in full. The references logic is in the wrong table; it should be in the users_management and driver_management and they should be referencing salary.user_id
As in my comments above, business logic would indicate that salary is not the correct table to be joining to as it has its own different Primary Key (id) along with a creation timestamp, and as salary will change over time you will likely get multiple records for a user in this table. You should have a table holding the employee/user data and join to this instead
Also you have data in the salary table that maybe should be held elsewhere: bank details should be part of the employee details, not salary details (security and modelling) so salary.user_id REFERENCES user(id)
To understand properly what @aarone4 said (which is correct) you may be interested in learning about Database Normalization, 1NF + 2NF + 3NF + Boyce-Codd is enough for the major part of targets if not every one you will use.
Those are a set of rules to use when designing DB models and relations and as you get skilled with them you'll define models that are directly compliant with those NFs, meanwhile just start with UNF (Unformalized Form) and proceed adding NFs one after another in this order (1NF, 2NF, 3NF, BCNF).
Best regards
thanks 😊