CODEWITHSUNDEEP
sqldatabase-design
thormayer
thormayer

Reputation: 1070

"2-way street" design

I have a project that I have there "companies" and "employees" and I need to be able to "subscribe" for both of them.

So I thought to make it straightforward and make in my SQL tables a table for companies and then every company has employees inside of it.

The thing is, that there is a possibility that some employees are also "registered" to more then one company.

So it's like a 2-way street thingy and I wonder how can I arrange my SQL tables properly?

I need to be able to "subscribe" to a company as well as a single employee (and then to present all of its works in all the companies he worked).

Upvotes: 0

Views: 54

Answers (1)

abhi
abhi

Reputation: 3136

I would do this with 3 separate tables instead of 2.

Here's my simplified model of the database. Since I am currently logged on to SQL Server, I used that database.

CREATE TABLE company 
  ( 
     company_id   INT, 
     company_name NVARCHAR(100) 
  ); 

CREATE TABLE employee 
  ( 
     employee_id   INT, 
     employee_name NVARCHAR(100) 
  ); 

CREATE TABLE employment 
  ( 
     company_id  INT, 
     employee_id INT, 
     startdate   DATE, 
     enddate     DATE 
  );

Upvotes: 1

Related Questions