Post reply

Schema Design

  • April 26, 2017 at 12:27 am

    #337495

    Hello,
    Good Afternoon.
    I would like to known whether below DDL suites best.
    Explanation: After the job creation details are created each details furthur formed in to batch's as highlighted in multicolor.

    Please refer to the below images for your kind perusal and give your valuable feedback.
    Note: Constraints are not applied to tables yet.

    Thank you.

    /* Main Table */
    CREATE TABLE [dbo].[BatchCreationHeader](
    [BatchId] [int] IDENTITY(1,1) NOT NULL,
    [BatchNo] [nvarchar](50) NOT NULL,
    [BatchDate] [date] NOT NULL,
    [JobCreationId] [int] NOT NULL,
    [StartJobDate] [date] NOT NULL,
    [StartShiftId] [tinyint] NOT NULL,
    [EndJobDate] [date] NOT NULL,
    [EndShiftId] [tinyint] NOT NULL,
    [BatchQty] [int] NOT NULL,
    [Note] [nvarchar](1000) NULL,
    [StatusId] [tinyint] NOT NULL);

    /* Junction Table */
    CREATE TABLE [dbo].[BatchCreationDetails](
    [BatchId] [int] NOT NULL,
    [JobCreationDetId] [int] NOT NULL);

  • April 26, 2017 at 8:17 am

    #1939976

    Something I'm not sure about here, it's clear from your example data a Batch can have multiple JobCreationDetail rows, but can a JobCreationDetail row belong to more than one Batch?  If so, then yes you would need the junction table, otherwise could this be simplified by just adding a column and foreign key in the JobCreationDetail table for the BatchID to reverence the Batch each row belongs to.

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

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