How to create an SQL column that can have multiple values?-CodePudding

I created a table in SQL using PostgreSQL called "tenants". Below is the code for the tenants:

create table tentants (
id bigserial not null primary key,
tenant_name varchar(1000) not null,
offices int not null,
number int not null,
email varchar(1000)

I want to include the ability to add multiple values to "office" in case a tenant rents more than one office. I don't want to use JSON for this. I tried creating a related table called "offices" but That could only allow me to add one office per tenant. What is the best approach for this?

CodePudding user response：

You can use text, that works for me with ids separated by commas like this

4,3,67,2

Anyway the proper approach would be another table and name it tenant_offices

tenant_offices
  columns >
    tenant_id
    office_id (well ofcourse you should have atleast an office table)

CodePudding user response：

You can create an "tenant_offices" table (like you did before), having as structure : id, tenant_id, office_id,... where id is the primary key of the "tenant_offices" table and tenant_id and office_id are foreign keys.

tenant_id which refers to your tenants table and office_id which refers to your offices table.

Here, the tenant can therefore rent several offices.

Hoping to have enlightened you, or helped !

CodePudding user response：

I assume that the relationship is one-to-many tenant to office (that is, office can be rented only by one tenant)

Then you have to create table offices with a foreign key that points to the tenant:

CREATE TABLE offices (
id bigserial not null primary key,
tenant_id bigserial foreign key references tenants(id))
additional columns if needed

note that in this version you will not retain history of rents (you will have to run update on offices to change tenant_id)

EDIT: In case of a many-to-many relationship (that will also allow us to retain history of rents) we need to create a relationship table:

CREATE TABLE TenantsOffices (
id bigserial not null primary key
tenant_id bigserial foreign key references tenants(id),
office_id bigserial foreign key references offices(id),
start_date datetime,
end_date datetime)

Useful information: https://www.sqlshack.com/learn-sql-types-of-relations/