asked
Do I Need a "Master" table?
Hello, 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.
@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: - [Hierarchies on Steroids #1][1] - [Hierarchies on Steroids #2][2] 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. [1]:
http://www.sqlservercentral.com/articles/Hierarchy/94040/ [2]:
http://www.sqlservercentral.com/articles/T-SQL/94570/
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.
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
19 People are following this question.
