question

postonoh avatar image
postonoh asked

normalizing database

I have a database with the following table Client, BidPackage, Project, Contractor, ProjectSelector, ContractorSelector The Project table the columns are: project_Id ProjectName ProjectNumber The Contractor table columns are: [Trade_ID] [int] IDENTITY(1,1) NOT NULL, [Contractors] [nvarchar](50) NULL, [ContactPerson] [nvarchar](50) NULL, [EmailAddress] [nvarchar](50) NULL, [Address] [nvarchar](150) NULL, [City] [nvarchar](50) NULL, [State] [nvarchar](50) NULL, [ZipCode] [numeric](18, 0) NULL, [BusinessPhone] [numeric](18, 0) NULL, [FaxPhone] [numeric](18, 0) NULL, [CellPhone] [numeric](18, 0) NULL, [User_ID] [int] NULL, [EntryDate] [datetime] NULL, [Client_id] [int] NULL, [ContractorSelector_ID] [int] NULL [ProjectSelector_ID] [int] NULL I created the following additional tables ProjectSelector the columns are ProjectSelector_ID int identity not null, Prime bit not null, CM_GC bit not null. Prime is true, and CM_GC is false Prime is false and CM_GC is True I created another table called ContractorSelector columns are ContractorSelector_ID int identity not null, Trade_Contractor bit not null, Trade_SubContractor bit not null Trade_Contractor is true or Trade_Contractor is false Trade_SubContractor is false or Trade_SubContractor is True Now here is what I need. A contractor can be what is call a Prime, GC_CM on a Project and on a BidPackage a Contractor can be a Trade_Contractor or a Trade_Subcontractor on a BidPackage. So in my BidPackage Table do I need to add both the ProjectSelector and ContractorSelector ID as fk or just the ContractorSelector_id.
sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
I assume a BidPackage belongs to a Project. I'd say you need two tables. One to show a Contractor being on a Project and in which role, and the other to show a Contractor being on a BidPackage and in which role. Something like this: **ProjectContractor** ProjectID ContractorID IsPrime IsCM_CG **BidPackageContractor** BidPackageID ContractorID IsSubContractor
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

postonoh avatar image postonoh commented ·
Right, the connection are bidpackage has a fk project_ID, contractor has a project_ID and bidpackage_id. I guess from the comment below I need to add Contractor_ID to the ProjectSelector as fk and the same ContractorSelector.
0 Likes 0 ·
postonoh avatar image postonoh commented ·
Also I need to address this it should be on one Trade Contractor for a bidpackage but can many Trade SubContractors for the bidpackage any suggestion.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
You should normalize a little bit further and move the following columns into different tables, add a primary key and use the primary key in Contractor table as a foreign key. [City] [nvarchar](50) NULL, [State] [nvarchar](50) NULL, By doing that, you will save a lot of space in contractor table, and a lot of IO operations. nvarchar(50) will take two bytes per character in the field + two bytes overhead. Lets say you have an average of 30 chars in each field, then you will store 124bytes for each record. This storage is compared to 8 bytes per record if you use two integer fields instead. Another side effect is that if the record is wide, few records will fit into each datapage (8Kb), and that will effect the size, depth, locking issues and the page splits of the indexes.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

postonoh avatar image postonoh commented ·
O.K. I have a the following table Client, BidPackage, Project, Contractor, ProjectSelector, and ContractorSelector. I should the primary key from the Contractor as fk in the both the ProjectSelector and ContractorSelector. So I should us city char 50 and char 50
0 Likes 0 ·
postonoh avatar image postonoh commented ·
Also I need to address this it should be on one Trade Contractor for a bidpackage but can many Trade SubContractors for the bidpackage any suggestion.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.