Professional Web Applications Themes

Solid Schema or NOT?!? - Microsoft SQL / MS SQL Server

Can someone tell me if this schema design is good or what I need to change in your opinion. I will respond back to any replies, so please check. Thanks everyone. CREATE TABLE [dbo].[Storages] ( [StorageID] [int] IDENTITY (100, 1) NOT NULL PRIMARY KEY, [EmpID] [int] NOT NULL , [StorageName] [varchar] (50) NOT NULL , [StreetAddress] [varchar] (150) NOT NULL , [City] [varchar] (50) NOT NULL , [State] [char] (2) NOT NULL CHECK ([State] like '[A-Z][A-Z]'), [PostalCode] [char] (5) NOT NULL CHECK ([PostalCode] like '[0-9][0-9][0-9][0-9][0-9]'), [Country] [varchar] (25) NOT NULL DEFAULT 'USA', [Phone] [char] (12) NULL CHECK ([Phone] like '[0-9] ...

  1. #1

    Default Solid Schema or NOT?!?

    Can someone tell me if this schema design is good or what
    I need to change in your opinion. I will respond back to
    any replies, so please check. Thanks everyone.

    CREATE TABLE [dbo].[Storages] (
    [StorageID] [int] IDENTITY (100, 1) NOT NULL
    PRIMARY KEY,
    [EmpID] [int] NOT NULL ,
    [StorageName] [varchar] (50) NOT NULL ,
    [StreetAddress] [varchar] (150) NOT NULL ,
    [City] [varchar] (50) NOT NULL ,
    [State] [char] (2) NOT NULL CHECK ([State]
    like '[A-Z][A-Z]'),
    [PostalCode] [char] (5) NOT NULL CHECK
    ([PostalCode] like '[0-9][0-9][0-9][0-9][0-9]'),
    [Country] [varchar] (25) NOT NULL DEFAULT 'USA',
    [Phone] [char] (12) NULL CHECK ([Phone] like '[0-9]
    [0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') ,
    [Priority] [char] (1) NOT NULL CHECK ([Priority]
    like 'P' or [Priority] like 'S'),
    [Comments] [varchar] (350) NULL ,
    [DateCreated] [datetime] NULL DEFAULT (getdate()),
    [UserName] [varchar] (25) NULL DEFAULT (suser_sname
    ())
    )
    GO

    CREATE TABLE [dbo].[Addresses] (
    [AddressID] [int] IDENTITY (100, 1) NOT NULL
    PRIMARY KEY,
    [EmpID] [int] NOT NULL ,
    [AddressType] [char] (1) NOT NULL CHECK
    ([AddressType] like 'P' or [AddressType] like 'S'),
    [StreetAddress] [varchar] (150) NOT NULL ,
    [City] [varchar] (50) NOT NULL ,
    [State] [char] (2) NOT NULL CHECK ([State]
    like '[A-Z][A-Z]'),
    [PostalCode] [char] (5) NOT NULL CHECK
    ([PostalCode] like '[0-9][0-9][0-9][0-9][0-9]'),
    [Country] [varchar] (25) NOT NULL DEFAULT 'USA',
    [DateCreated] [datetime] NULL DEFAULT (getdate()),
    [UserName] [varchar] (25) NULL DEFAULT (suser_sname
    ())
    )
    GO

    CREATE TABLE [Employees] (
    [EmpID] [int] NOT NULL PRIMARY KEY,
    [TeamID] [int] NULL ,
    [LarkID] [varchar] (10) NULL ,
    [AcctCustCode] [char] (6) NULL CHECK
    ([AcctCustCode] like '[0-9][A-Z][A-Z][A-Z][A-Z][A-Z][A-
    Z]'),
    [BaseCity] [varchar] (50) NULL ,
    [Region] [varchar] (25) NULL ,
    [TerritoryCode] [char] (6) NULL CHECK
    ([TerritoryCode] like '[A-Z][A-Z][0-9][0-9][0-9][0-9]'),
    [TerritoryCode1] [char] (6) NULL CHECK
    ([TerritoryCode1] like '[A-Z][A-Z][0-9][0-9][0-9][0-9]'),
    [FirstName] [varchar] (50) NOT NULL ,
    [LastName] [varchar] (50) NOT NULL ,
    [DateEffective] [datetime] NULL ,
    [DateHired] [datetime] NOT NULL ,
    [PositionType] [char] (4) NOT NULL CHECK
    (PositionType like 'FULL' OR PositionType like 'FLEX'),
    [Title] [varchar] (25) NOT NULL,
    [DepartmentID] [int] NOT NULL,
    [Home] [char] (12) NULL CHECK ([Home] like '[0-9]
    [0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') ,
    [HomeOffice] [char] (12) NULL CHECK ([HomeOffice]
    like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-
    9]') ,
    [Work] [char] (12) NULL CHECK ([Work] like '[0-9]
    [0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') ,
    [Fax] [char] (12) NULL CHECK ([Fax] like '[0-9][0-
    9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') ,
    [Cell] [char] (12) NULL CHECK ([Cell] like '[0-9]
    [0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') ,
    [Email] [varchar] (50) NULL,
    [Extension] [smallint] NOT NULL ,
    [NTLogon] [varchar] (35) NOT NULL,
    [Photo] [varchar] (50) NULL ,
    [ReportsTo] [varchar] (100) NOT NULL ,
    [IsActive] [char] (1) NULL CHECK ([IsActive]
    like 'Y' or [IsActive] like 'N'),
    [EmpTypeID] [int] NOT NULL ,
    [Notes] [varchar] (350) NULL ,
    [DateCreated] [datetime] NULL DEFAULT (getdate()),
    [UserName] [varchar] (25) NULL DEFAULT (suser_sname
    ())
    )
    GO

    CREATE TABLE [dbo].[TeamTypes] (
    [TeamID] [int] NOT NULL PRIMARY KEY,
    [TeamType] [varchar] (25) NOT NULL,
    [DateCreated] [datetime] NULL DEFAULT (getdate()),
    [UserName] [varchar] (25) NULL DEFAULT (suser_sname
    ())
    )
    GO

    CREATE TABLE [dbo].[EmpTypes] (
    [EmpTypeID] [int] NOT NULL PRIMARY KEY,
    [EmpType] [varchar] (25) NOT NULL,
    [DateCreated] [datetime] NULL DEFAULT (getdate()),
    [UserName] [varchar] (25) NULL DEFAULT (suser_sname
    ())

    )
    GO

    CREATE TABLE [dbo].[Departments] (
    [DepartmentID] [int] NOT NULL PRIMARY KEY,
    [DepartmentName] [varchar] (40) NOT NULL
    )
    GO

    ALTER TABLE [dbo].[Addresses] ADD
    FOREIGN KEY
    (
    [EmpID]
    ) REFERENCES [dbo].[Employees] (
    [EmpID]
    )
    GO

    ALTER TABLE [dbo].[Storages] ADD
    FOREIGN KEY
    (
    [EmpID]
    ) REFERENCES [dbo].[Employees] (
    [EmpID]
    )
    GO

    ALTER TABLE [dbo].[Employees] ADD
    FOREIGN KEY
    (
    [TeamID]
    ) REFERENCES [dbo].[TeamTypes] (
    [TeamID]
    )
    GO

    ALTER TABLE [dbo].[Employees] ADD
    FOREIGN KEY
    (
    [DepartmentID]
    ) REFERENCES [dbo].[Departments] (
    [DepartmentID]
    )
    GO

    ALTER TABLE [dbo].[Employees] ADD
    FOREIGN KEY
    (
    [EmpTypeID]
    ) REFERENCES [dbo].[EmpTypes] (
    [EmpTypeID]
    )

    Alex Guest

  2. #2

    Default Re: Solid Schema or NOT?!?

    Alex,

    Generally, without a thorough understanding of your business model, one
    cannot comment on the correctness of a schema. However, general suggestions
    are possible based on the information you provide. Could you briefly explain
    what this DDLs represent? What are the entities in your model? How are they
    related ? What are the attributes for these entities? What are the
    dependencies among them? The answers to these questions help understand if
    your logical schema design is correct or not.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: Solid Schema or NOT?!?

    Actually, proper design involves accurately yzing the business model,
    applying relational principles on the model and implementing the model using
    a DBMS. This requires, as mentioned before, an exhaustive understanding of
    your business requirements. Since you mentioned only about Employees,
    Addresses & storages and only the 1-to-many relationships between employees
    and addresses as well as employees and storages, one way of logical
    representation in SQL is :

    CREATE TABLE Employees (
    Emp_id INT NOT NULL PRIMARY KEY,
    ...);

    CREATE TABLE EmployeeAddresses (
    Address_id INT NOT NULL PRIMARY KEY,
    Emp_id INT NOT NULL
    REFERENCES Employees(Emp_id)
    ...);

    CREATE TABLE EmployeeStorage (
    Storage_id INT NOT NULL PRIMARY KEY,
    Emp_id INT NOT NULL
    REFERENCES Employees(Emp_id),
    ...);

    Note, that it is quite possible that with a many-to-many relationship (an
    address used by multiple employees or many employees sharing a single
    storage), the above representation becomes under-normalized and thus
    inadequate. In such a situation a better logical representation will be :

    CREATE TABLE Employees (
    Emp_id INT NOT NULL PRIMARY KEY,
    ...);

    CREATE TABLE Addresses (
    Address_id NOT NULL PRIMARY KEY,
    ...);

    CREATE TABLE Storage (
    Storage_id NOT NULL PRIMARY KEY,
    ...);

    CREATE TABLE EmployeeAddresses (
    Address_id INT NOT NULL
    REFERENCES Addresses(Address_id)
    Emp_id INT NOT NULL
    REFERENCES Employees(Emp_id)
    ...
    PRIMARY KEY (Emp_id, Address_id));

    CREATE TABLE EmployeeStorage (
    Storage_id INT NOT NULL
    REFERENCES Storage(Storage_id)
    Emp_id INT NOT NULL
    REFERENCES Employees(Emp_id),
    ...
    PRIMARY KEY (Emp_id, Storage_id));

    As you can see, this logical model will be flexible enough to accomodate
    changes without causing update/delete anomalies and can be considered
    sufficiently normalized ( only based on the assumptions above, though ).

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

Similar Threads

  1. Solid lines
    By Zaid_Faydi@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 9
    Last Post: August 6th, 09:42 AM
  2. Need some solid Help. With DG and CFC Array
    By rottmanja in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: February 28th, 04:43 PM
  3. OT: solid ink printers
    By Bill in forum Macromedia Freehand
    Replies: 1
    Last Post: January 13th, 03:28 PM
  4. solid ink printers
    By darrel in forum Macromedia Freehand
    Replies: 0
    Last Post: January 12th, 03:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139