Designing a table

  • 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.

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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)
    Intro to Tally Tables and Functions

  • 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.

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

    1. a company can have many workers(01,02)  Company ---(1,M)--Worker
    2. worker can have multiple jobs (jb01, jb02) within the company,  Company ---(1,M)--Worker--(1,M)--Job
    3. 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?)

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

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