Database Design: Table structure

Question

I've excel form which contain macro that can pull data from SQL Server and display info on the excel form. Users can update the form and macro will write changed back to SQL Server.

This is the excel form. Users need to input the values for those highlighted on yellow color. Example John enter user field as 'JOHN' follow by region 'LATAM' , year '2016' and month '3'. Once it done, John would click an 'Retrieve' button to pull all data from SQL Server where Region = LATAM, Year=2016 and Month = 3. All the related info would display on field Area, Region, Mgmt, CompanyCode, AcctUnit, Account and Value (It could have multiple rows of records and users are not allow to edit these records). Now John need to input value for field Comments. Once he completed, John would click 'Save' button and excel macro would write comments value to SQL Server

Here the table structure that excel macro will read and write

CREATE TABLE [dbo].[Sales](
[Region] [varchar](10) NOT NULL,
[Area] [varchar](10) NOT NULL,
[Mgmt] [varchar](10) NOT NULL,
[CompanyCode] [int] NOT NULL,
[AcctUnit] [varchar](7) NOT NULL,
[Account] [varchar](10) NOT NULL,
[Comment] [varchar](100) NULL,
[Year] [int] NULL,
[Jan] [float] NULL,
[Feb] [float] NULL,
[Mar] [float] NULL,
[Apr] [float] NULL,
[May] [float] NULL,
[Jun] [float] NULL,
[Jul] [float] NULL,
[Aug] [float] NULL,
[Sep] [float] NULL,
[Oct] [float] NULL,
[Nov] [float] NULL,
[Dec] [float] NULL,
[SYS_CreatedBy] [varchar](15) DEFAULT SYSTEM_USER,
[SYS_CreatedDate] [datetime] DEFAULT GETDATE(),
[SYS_ModifiedBy] [varchar](15) DEFAULT SYSTEM_USER,
[SYS_ModifiedDate] [datetime] DEFAULT GETDATE(),
CONSTRAINT [PK_Sales] PRIMARY KEY NONCLUSTERED
    (
    Region ASC, 
    Area ASC, 
    Mgmt ASC,
    CompanyCode ASC,
    AcctUnit ASC,
    Account ASC,
    )
)

Based on above table structure, how can other users put his/her comments while it doesn't affected those comments that John already updated to SQL Server ? Example, John already input his comments and has been updated to SQL Server, now Alice pull same data from SQL Server where region = LATAM, Year = 2016 and Month = 3. She should get same data as John did except comments field, which in this case should be blank instead of John's comment.

Based on above table design, the comment's value will keep changing by different users. How to redesign table structure so comment can be unique to each users?

The comment column should not be in this table. It should be in a SalesComments table. Also, you really shouldn't have columns for each month. You should have a SaleDate columns and an Amount column. This whole table is in serious need or normalization. For example, region should NOT be in this table. You should have the customer and the customer would be assigned a region. Same thing with many other columns. Also, be careful using float to hold sales information. It is an approximate datatype that can't hold every value accurately. — Sean Lange, May 09 '16 at 14:54
You should add an ID column to the Sales that is unique (maybe SalesID. Then break out the Comments into its own table with a foreign key back to SalesID. That way you can have multiple comments and keep track of who made them and when without duplicating all of the rest of your data. This process of removing called "database normalization". See answer and comments on http://stackoverflow.com/questions/723998/what-are-1nf-2nf-and-3nf-in-database-design for more information. — Isaac, May 09 '16 at 14:56
A relational database is created by a process known as [database normalization](https://en.wikipedia.org/wiki/Database_normalization). — Gilbert Le Blanc, May 09 '16 at 15:28

score 0 · Accepted Answer · answered May 09 '16 at 14:52

0

I would create a Comments table, and relate it to the Sales table through a foreign key.

To that end, I would add a surrogate key to the Sales table, so that you don't need to add all those PK columns to your Comments table.

answered May 09 '16 at 14:52

Tab Alleman

31,483
7
36
52

Database Design: Table structure

1 Answers1