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