Designing a table

Question

Designing a table

Johnson330

Old Hand

Points: 345
More actions
June 29, 2022 at 8:30 am

#4056438

I have a scenario where:
a company can have many workers(01,02) & worker can have multiple jobs (jb01, jb02) within the company, and in each job the worker can have 3 training courses (tr01,tr02,tr03) every year (2020,2021,2022).
I need to create one table to accommodate this scenario to record the courses each worker did in each job every year .
Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

Homework?

A company can have many workers. So, you already have two tables. Company has many workers. Probably the company is the parent, the worker is the child. So, the PK for the company becomes a column (or columns) in your worker table.

Since this is homework, I'm not doing it all, since I won't get credit for it. Continue the exercise from there.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Johnson330 wrote:

I have a scenario where: a company can have many workers(01,02) & worker can have multiple jobs (jb01, jb02) within the company, and in each job the worker can have 3 training courses (tr01,tr02,tr03) every year (2020,2021,2022). I need to create one table to accommodate this scenario to record the courses each worker did in each job every year . Thank you.

Without an ERD of the tables involved, the only thing I can say is to check the FKs for the tables and create query with joins based on those FKs. If there are no FKs, you'll need to figure out how the tables relate by looking at column names and the data itself. You might be able to get some hints by looking at views, functions, and procedures that contain at least two of the table names you cited.

A better thing to do is to ask an SME (Subject Matter Expert) at the company that works with the data on a frequent basis).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

Johnson330 wrote:

I need to create one table to accommodate this scenario to record the courses each worker did in each job every year.

Whoever asked you to do this needs to be taken outside and given a good kicking.

Your scenario requires the creation of multiple related tables, as Grant has already suggested. If you try to put this into a single table, there will be a horrific amount of data duplication.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

pietlinden SSC Guru Points: 63428 More actions · Answer 4

If I were you, I would get out a whiteboard (or piece of paper) and draw / diagram this:

a company can have many workers(01,02) Company ---(1,M)--Worker
worker can have multiple jobs (jb01, jb02) within the company, Company ---(1,M)--Worker--(1,M)--Job
and in each job the worker can have 3 [several] training courses (tr01,tr02,tr03) every year (2020,2021,2022).
This is a "roster" type question... (Can more than one person have the same training course? How do you model that?)