Can a table have more then one detail group? I need 4 detail groups show from a parent group. They all come from the same query dataset. Or is the a better way to do this?
Thanks
You can't have multiple detail groups in a table. However, you can add more rows for the parent group. Then in each of the rows, add a nested table/list to show the details.Currently I need to design database table and got stuck.
I have a project and its sub projects. A parent project and its sub projects share the same requirement.The description of the requirement will be changed every day.
For example
Project A - Do Some thing 1 on Project A [09/28/2007]
Project A-A1 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]
Every time the comments updated, both project A and project A-A1 should share the same update i.e select project A and its subproject will be displayed
Project A Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]
Project A-A1 Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29//2007]
When you tried to insert new sub project, the requirement will be shared with the parent project ie.
Project A - Do Some thing 1 on Project A [09/28/2007]
Project A-A1 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]
Project A-A2 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007] , Do Some thing 2 on Project A-A2 [09/30/2007]
assume Project A-A1 and Project A-A2 are sub project of project A
Could anyone show me how to design the database table for those mess?
Thank you
You need 3 table
ProjectGroup
id
name
Project
id
groupID -> ProjectGroup.id
name
ProjectLog
id
groupID ->ProjectGroup.id
note
date_add
Regards,
Max
|||My previous post don't actually work, my bad.
ProjectGroup
id
name
Project
id
groupID -> ProjectGroup.id
name
ProjectGroupLog
id
groupID ->ProjectGroup.id
note
date_add
ProjectLog
id
projectID ->Project.id
note
date_add
Now when you need to display the logs, just merge the ProjectGroupLog result with the ProjectLog.
|||Isn't this a self-referential ParentID relationship? You can do it all in one table (I think):
tbProject
ProjectID int IDENTITY (1,1) (PK)
ProjectName nvarchar(255)
ParentProjectID int NOT NULL (FK --> projectID)
TopLevelProjectID int NOT NULL (FK --> ProjectID)
Requirement nvarchar(255)
Create a project, it gets ProjectID = 1, and since it has no Parent, it's ParentID = 1 that means that it's a Top-Level project, so it's TopLevelProjectID = 1.
A sub project would have ProjectID = 2 (or whatever the next avilable ID is), a ParentProjectID = 1, and a TopLevelProjectID = 1.
And, if this sub had a sub, then:
ProjectID = 3 (or next available ID)
ParentID = 2 ('cause that's one level UP from this project)
TopLevelProjectID = 1 ('cuase that the top of the tree from this project)
With this, you can use something like:
Update Set REquirment = Requirment + " SOME NEW STRING " where TopLevelProject = 1
|||
Code Block
create table #Projects(
ProjectID int identity(1,1)
, ParentID int
, TopLevelID int
, ProjectName varchar(255)
, ProjectRequirement varchar(255)
)
insert into #Projects(ProjectName,ParentID,TopLevelID,ProjectRequirement)
select 'Project # 1', 1, 1,'some thing or other'
insert into #Projects(ProjectName,ParentID,TopLevelID,ProjectRequirement)
select 'Project # 2', 2, 2, 'do this now'
insert into #Projects(ProjectName,ParentID,TopLevelID, ProjectRequirement)
select 'Project # 3', 3, 3, ' FOOBAR the 3rd'
insert into #Projects(ProjectName,ParentID,TopLevelID)
select 'Project # 1: Sub A', 1, 1
insert into #Projects(ProjectName,ParentID,TopLevelID)
select 'Project # 2: Sub A', 2, 2
insert into #Projects(ProjectName,ParentID,TopLevelID, ProjectRequirement)
select 'Project # 1: Sub B', 1, 1, 'Hey, here''s another one!!'
insert into #Projects(ProjectName,ParentID,TopLevelID)
select 'Project # 1: Sub A: Sub i', 4, 1
insert into #Projects(ProjectName,ParentID,TopLevelID)
select 'Project # 1: Sub A: Sub ii', 4, 1
insert into #Projects(ProjectName,ParentID,TopLevelID, ProjectRequirement)
select 'Project # 1: Sub A: Sub ii: Sub a:', 8, 1, 'Do it. Do it now!'
insert into #Projects(ProjectName,ParentID,TopLevelID)
select 'Project # 3: Sub A', 3, 3
insert into #Projects(ProjectName,ParentID,TopLevelID)
select 'Project # 3: Sub A: Sub i', 10, 3;
with CTE
as
(
select ProjectName as [ProjectName],ProjectName as [ParentProject],ProjectName as [TopLevelProject], convert(varchar(4000),IsnUll(ProjectRequirement,'')) as [REQ], ProjectID from #Projects where TopLevelID = ProjectID
union all
select P.ProjectName,cte.[ProjectName],cte.[TopLevelProject], convert(varchar(4000),CTE.REQ + ': ' + isnull(ProjectRequirement,'')), p.ProjectID
from CTE inner join #Projects P on P.ParentID = cte.ProjectID
where P.ProjectID <> p.ParentID
and p.ProjectID <> p.TopLevelID
)
select * from CTE order by TopLevelProject, ParentProject
drop table #Projects
You can accomplish this with 2 tables. Parent Table and Child Table or Project Table and Sub Project Table.
Project Table
-->ProjectNo
-->Comment1
-->Comment2
SubProject Table
-->ProjectNo FK
-->SubProjectNo
-->SubComment1
-->SubComment2
Your update on your SubProject would reflect in the Project as follows:
UPDATE sp
SET sp.SubComment1 = 'Did some work', p.Comment1 = 'Did some work on SubProject' & 'A1'
FROM SubProject sp
JOIN Project p on p.ProjectNo = sp.ProjectNo
WHERE sp.SubProjectNo = 'A1' AND p.ProjectNo = 1
As far as your insert statement is concerned, you would use an ON INSERT trigger on the Project Table to create a subrecord in the subproject table.
Adam
I have built some cubes for one of two companies owned by a parent company. The execs at the parent company like what they see. They want the same thing done for the second company. They would also like to see a rolled up view of the data (sales, ytd sales by product group, material cost...).
Should I:
Build separate fact tables and cubes. If so how best to roll up the data. I would have to combine sales for identical product groups etc.
Another idea I had was to include an entity_id column in the fact tables and keep everything together.
My feeling is that the entity_id would be easy but some columns will be missing data for one of the company and other columns may be empty for the other company.
There are certainly issues with either method (security, performance, maintenance) but I would like to hear what your thoughts are.
By the way, the parent company just purchased a third company so I that company will eventually be included.
Thanks,
Chris
This is not an SSAS2005 issue but since I cannot find a data warehouse modeling group I can give you some general advice:
Have a look here: http://www.kimballgroup.com/ for design issues. Most of the content is free and of good quality.
A company dimension is recommended. Be careful, though, with internal sales, between the companies in the group, and how this is handled.
Normally the parent company would like a consolidated view of their business and this means, at least, common dimensions and common fact tables.
Kind Regards
Thomas Ivarsson
I don't know if this is the right forum but...
In a parent/child table structure (order/orderdetail) I have used identity columns for the orderdetail or compund primary keys. I find a single identity column on the detail table easier to manage (with a fk to the parent) but what ends up bieng easiest for the user is to have an order (say #3456) and detail items listed sequentially from 1 to n. This reflects a compound key structure but generating the 2nd field is a pain. Is there any way to tie an identity field to the parent key so that it will generate this number for me automatically?
Nope. But, that also brings up a very interesting point. Why is your user directly querying the data within the table in such a way that they would even see this value? It's an internal structural element and should not have any business meaning at all. Put an identity on the orderdetail table, link it to it's parent OrderID in the orders table. Now, in the application which your users should be using to work with data, yank the order detail rows out, display them in ascending order of the OrderDetailID, and then on the application tier just number the lines from 1 - N. The user now gets the display that they want and you aren't tying a structural element to a business meaning.
Why wouldn't you want to give this key a business meaning? Very simple. User enters an order with 10 line items. They then decide to delete lines items 3, 6, and 7. If you are physically storing this data, you would then have to update the foreign keys and then the primary key (parent) upon which they depend which is something that should never be done.
|||The users are not directly querying the data. The might see it in a report. Users see keys all the time and they are very useful. An invoice number is a key, a UPS tracking number is a key, a Bloomberg trouble ticket is a key. They are very useful in tracking down problems. When these objects have detail items users do communicate using the codes ("I have a problem with order #2356, item #5"). I was trying to find a way to make this easy by keeping the 2nd number low ("I have a problem with order #2356, item #58694937").
I wouldn't need to delete the rows, I could mark them as deleted and strike through them on a report or just eliminate them from the report and have gaps in the numbering.
|||So the answer to that is you have to custom code that yourself which is also going to mean performance, scalability, and concurrency issues.