Do I Need a "Master" table?


Currently, I have a projects table that stores all of our company's projects. It has a primary key (INT IDENTITY). Now one would think that each project would be ONE project, but that is not the case. In my situation, we one project with the possibility of multiple sub-projects. For example, in my business (construction) we'll have a project to build an office building. A project number is assigned, and then there are additional projects set up, much like phases. Using the office example, there could be a project set up for pre-construction, another one for concrete/foundation, and so on. The project numbering goes something like this: master job: 1000, sub-jobs: 1000.01, 1000.02, and so on. I think that the best way forward is to create a derived table (dimension) for the master job. Doing so would provide me with a clearly defined relationship. Any input would be most appreciated.

more ▼

asked Jan 12 at 01:33 PM in Default

avatar image

120 5

Yes, it seems like it is kind of the same case of "Order" and "OrderDetail" relationship. But please share the DDL of respective tables to get the best opinion.

Jan 12 at 02:10 PM Usman Butt

@tlenzmeier Is there any possibility for the sub-project to have any "sub-sub-projects" under it? If this is the case then you might want to consider the adjacency list setup (self-referencing foreign key) or nested sets, which is much faster. The absolute best to read regarding these two is the 2-part Hierarchies on Steroids series by @Jeff Moden:

For SSAS multidimensional solutions the hierarchical dimensions are pretty easy to design, but in reality most people opt to "unwind" the data first rather than relying on the SSAS ability to create hierarchical dimensions.

Jan 12 at 02:24 PM Oleg

There would not be any "sub-sub" projects, just a master project and sub-projects. The source system doesn't have any kind of "master" project. Instead, it's simply a projects table.

create table [dim].[Project]( [ProjectKey] [int] identity(1,1) not null, [LegalEntityKey] [int] not null, [CompanyCode] [nchar](3) null, [CustomerKey] [int] not null, [ProjectDescription] [nvarchar](250) not null, [JobNumber] [nvarchar](10) not null, [OriginalContractAmount] [decimal](19, 5) null, [RowEffectiveDate] [datetime] not null, [RowEndDate] [datetime] null, [RowUpdateDate] [datetime] null, constraint [PK_Project] primary key clustered ( [ProjectKey] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY] ) on [PRIMARY] go

alter table [dim].[Project] add constraint [DF_Projects_Bonded] default ((0)) for [Bonded] go

alter table [dim].[Project] add constraint [DF_Projects_RowInsertDate] default (getdate()) for [RowEffectiveDate] go

alter table [dim].[Project] add constraint [DF_Project_RowEndDate] default (null) for [RowEndDate] go

Jan 12 at 02:44 PM tlenzmeier
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 12 at 01:33 PM

Seen: 49 times

Last Updated: Jan 12 at 02:44 PM

Copyright 2018 Redgate Software. Privacy Policy