Using UNION and GUID because of Knowledge limitations...

Question

Using UNION and GUID because of Knowledge limitations...

cidr

Ten Centuries

Points: 1353
More actions
November 30, 2008 at 12:40 pm

#99377

Hi, hope I can get help with this
The database I'm developing is for Project reporting. it has six tables, three of which, Milestone tables, have identical schemas and three other tables, PlanDetail tables, which have identical schemas. The Milstone tables are parents of the PlanDetail tables, so Milestone table(2) is parent of PlanDetail table(2).
They should really only be 2 tables, one Milestone table and one PlanDetail table but because of reasons to do with the frontend app there is the six tables.
for reporting I want to create two views. The first will have the three Milestone tables, I'll do a UNION and that will make only one Milestone field for example, rather than 3 Milestone fields. I'll do the same with the PlanDetail Tables and UNION them.
Then when I'm reporting I'll add the two views and Join the MilestoneID PK of the Milestone table to the MilestoneID FK of the PlanDetail table.
Here's where I need advice. I need all MilestoneIDs from all the Milestone tables to be Unique so I can do the JOIN with the views. This means that one Milestone table's ID can't have the same value as an ID field from another Milestone table.
I want to ask advice about this. My plan is to use a GUID rather than IDENTITY. I belive this will work until I've got the frontend app problem sorted so that I can use just one Milestone table and one PlanDetail table.
So when I get to the reporting services stage it will be like using the two tables like it should be, at least I hope.
In your opinion, will the GUID work in my situation? And, does anyone think I'll have problems with the UNION to create the Views?
Thanks for reading and for any help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I would think that changing an integer Identity column to a GUID would break the app.

I do not think that you should need this for the Reporting Services issue, you should be able to distinguish the tables through the use of the UNION:

CREATE View vwMilestone as

Select 1 as [Source], * From Milestone_1

UNION ALL

Select 2 as [Source], * From Milestone_2

UNION ALL

Select 3 as [Source], * From Milestone_3

go

CREATE View vwPlanDetail as

Select 1 as [Source], * From PlanDetail_1

UNION ALL

Select 2 as [Source], * From PlanDetail_2

UNION ALL

Select 3 as [Source], * From PlanDetail_3

go

Now when you do you Joins in Reporting Services, use both the Milestone ID columns and the "Source" columns that we created in the union as you join conditions:

Select *

From vwMilestone m

Join vwPlanDetail d

ON m.MilestoneID = d.MilestoneID

And m.Source = d.Source

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Hi

IMHO.. there is no need for a GUID. They come with a performance drag specially when you want to query a large number of rows. RbarryYoung's solution would work just fine.

However whats the difference in the data between the 3 tables (maintenance and plandetails). Maybe that column in each of the 3 tables could be made part of a unique key and be queried upon. This will also help when you merge the 3 tables into one.

Ex:- Lets assume that you have a column year in the MileStone table and Milestone_1 has the value 2000, Milestone_2 has value 2001 and so. Year column could be made a part of the unique/primay key and be used in the view for joining and later can be used while "merging" the 3 tables into 1. The example is pretty rudimentary and maybe you have something better in your case.

"Keep Trying"

cidr Ten Centuries Points: 1353 More actions · Answer 3

Sorry I haven't been in touch until now.

I've not tried it out yet because I've not got to the reporting stage, However, I'll be developing that side soon. I'll let you know how I get on.

Once again, thanks for the help.

🙂

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

cidr Ten Centuries Points: 1353 More actions · Answer 5

Hi folks,

I know it's been a while. I've now tried your suggestions RBarryYoung and it worked fine. Like to thank both of you for your help.

Paul

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Thanks for the feedback, Paul. It's always nice to know how a suggestion worked out.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]