PJ 2002 --> primary key's ??

[maarten]

Hi,

The MSP_xx tables created with PJ are all generated without any primary
key defined.

What is the reason for this somewhat unusual behaviour.


Thanks !

Maarten

 

[Rod Gill]

The plain text fields are mostly for you to read only. Project stores all
real data in the binary blob fields. No doubt there are technical reasons
for not having primary keys, but I've never seen an explanation!

 

[BrewskiAtBellSouth]

Oh, it goes waaay beyond the text fields, and "unusual" is a classic
understatement: of the 228 tables in the Project Server database, there isn't
a single instance of 1) a primary key; 2) referential integrity; 3) foreign
key indexing!!! Heck, most of the fields that one would consider important
just because MS put unique indexes on them are nullable; puhlease ...
Needless to say the integrity and performance of this database is nothing
short of HORRIBLE, and probably the single biggest example I use to
demonstrate how Microsoft pays lip service to "best practices". This is basic
work any competant DBA could do in a month,and I would give them that long
just because of the sheer volume of tables. (I have a script that I apply to
the MS2K3 database which incorparates my humble interpretation of proper
referential integrity - runs in a couple of minutes and improves performance
by orders of magnitude for any database with a significant number of records;
this is not rocket science ...) And, not even counting the PS2K2 product,
we're looking into this void after the product has been available for
purchase for 3+ years & been through 2 service packs. I haven't seen the
proposed structure for the PS2K7 databases, but if it isn't a SIGNIFICANT
improvement over PS2K3, I certainly won't be in any position to recommend the
new product to any of my clients. Shoddy is as shoddy does, Microsoft.

Bill Knight
MCDBA, MCSD, MCSE+I, MCP+SB, MCT( inactive)

 

[Rod Gill]

Yes but Microsoft Project does not read the text data unless Ext flags are
set. All data is in the binary blobs instead. I feel it is highly unlikely
that Microsoft wouldn't use PK's if their use would make a big difference
for this scenario.

They are not publishing the schema for the 2007 database as they do not want
people using and updating it. They are reserving the right to change the
schema as needed. All updates should be done via PSI or Office automation in
Project Professional. All reporting should be done using the new reporting
database for which a schema has already been added to the latest SDK (I
think).

 

[BrewskiAtBellSouth]

"All data is in the binary blobs instead"

Not sure what you mean by that, Rod. If you run SQL Profiler while creating
a simple (one resource, one task) project & publishing it to Project Server,
I don't see a whole lot of binary data being pushed around. Here's an example
of a query (yes, raw SQL, not a stored proc) that is probably representative
of what you will see:

***********************************************************
SELECT
A.RES_NAME,A.RES_UID,A.RES_ID,A.RES_TYPE,A.RES_RTYPE,A.RES_CHECKEDOUT,A.RES_CHECKOUTBY,A.RES_CHECKOUTDATE,A.RES_ENTERPRISE_TIMESTAMP,A.RES_PHONETICS,
A.RES_AD_GUID FROM MSP_RESOURCES A WHERE RES_UID > 0 AND PROJ_ID = 1 AND
A.RES_RTYPE < 1000 AND A.RES_UID IN (select TokenVal from
dbo.MSP_WEB_FN_ConvertListToTable('1,2')) ORDER BY A.RES_UID
***********************************************************

For a new project, the only binary field (RES_PHONETICS) will be NULL, so it
isn't doing anything.

I'll have to stick to my assertion that the PS2K3 database is an abominable
example of relational database design & deployment, right up there with the
old SMS database (at least that one had PKs & some required fields). If MS
wants to build any credibility, they WILL publish the schema for the PS2K7
databases and show that they spent some time/money/resources getting it
right. They've been publishing the schema for the SQL Server system tables
for years (I have posters for three different versions hanging on the wall);
how could the schema for PS2K7 be any more sensitive?

B@BS

 

[Gary L. Chefetz [MVP]]

Babs:

Rod is correct. The contents of the tables you see in the database as "MSP_"
anything do not contain the active data that the system is processing. All
of the "MSP_" data content is extracted from the binary record stored as a
single field in the "MSP_PROJECTS" table. The "MSP_WEB_" data is the active
data source for Project Server/PWA added features. Understand that all of
the actual project record data is stored as binary. Not all of the data
comprising a project is available through the database tables. Much remains
accessible only through the Project Object Model.

The 2007 approach is a step in the right direction and toward your approval
as it promises to give us a fully functional API. I suppose we could get
into a debate about logical database design versus physical database design,
and how normalization may be the goal for the former and not necessarily the
latter, but we can't put it into the context in which the Project product
team is operating in. Without knowing what the development constraints these
folks are facing, it's difficult for me to be too hyper-critical. Keep in
mind that Microsoft has a habit of trying to make everything backward
compatible, and at a certain point that makes it difficult to stage a
development revolution.

 

[BrewskiAtBellSouth]

1.) If "All of the ... content is extracted from the binary record stored as
a single field in the [MSP_PROJECTS] table", then why even have all of the
227 other tables? If "the contents of the tables you see in the database as
[MSP_] anything do not contain the active data that the system is
processing", then why do MS Project & MS Project Server execute stored
procedures & uncompiled queries (ugh) against those very tables?

2.) If [all of the actual project record data is stored as binary], my god,
what a great way to use the power of of a relational database! We'll just not
bother with all of that pesky indexing and referential integrity, let's just
turn SQL Server into a big ole bit bucket. And to disguise the fact that
anything that's actually important is stored as a blob, we'll surround the
blobs with a bunch of tables that look and feel like relational objects, but
are really just window dressing. Makes perfect sense.

3.) If 1 & 2 are ture, how come the response time decreases when I apply
proper (IMHO) indexing?

As a database guy, I really don't care about the API, do I? I care about the
well-being, integrity & response of the data, not how you get at it. Good
n-tier design (that thing that everybody talks about but nobody actually
does) requires (REQUIRES!) that the data store be totally ignorant of how I
access it. I should get the same beautiful response whether I request Project
Server data from MS Project using Microsoft's API, MS Excel via OLEDB/ODBC
or, good gawd a'mighty, my own custom API. I have three separate clients who
have evaluated and rejected Project Server because they couldn't get under
the covers and understand what was going on with the data and make it fit
their own existing company data. That's not me taking, that's the guys who
write the checks. Example: try to integrate a company employee list stored in
a separate SQL Server (or Oracle or whatever) table with the Project global
resource list, the goal being to add a new employee to the company table and
have it appear as a resource w/o having to enter/import/synchronize through
Project. A perfectly reasonable business request that can't be done with a
blob & the MS API, folks. What's Project Server gonna say about ten resources
named "John Smith"? Again, a perfectly reasonable business situation that I
can't address if I can't get under the covers.

And I understand development cycles and backward compatibility; but I don't
see many people riding horses to work in the morning or feeding their Chevys
hay & oats.

 

[Gary L. Chefetz [MVP]]

1) A good reason for all those tables is that in the current version you
need to develop against them. Remember that PWA is an application developed
against them. That doesn't change the fact that the binary blob is prime.
All you need to prove this is a corrupt project record.<g>

2) Again, a transitional strategy, although I think the new SharePoint
technologies will be the largest culprits in turning database servers into
bit buckets.

3) To respond to this you'd have to be more specific

I still open command windows. Ain't that hay and oats?

1.) If "All of the ... content is extracted from the binary record stored
as
a single field in the [MSP_PROJECTS] table", then why even have all of the
227 other tables? If "the contents of the tables you see in the database
as
[MSP_] anything do not contain the active data that the system is
processing", then why do MS Project & MS Project Server execute stored
procedures & uncompiled queries (ugh) against those very tables?

2.) If [all of the actual project record data is stored as binary], my
god,
what a great way to use the power of of a relational database! We'll just
not
bother with all of that pesky indexing and referential integrity, let's
just
turn SQL Server into a big ole bit bucket. And to disguise the fact that
anything that's actually important is stored as a blob, we'll surround the
blobs with a bunch of tables that look and feel like relational objects,
but
are really just window dressing. Makes perfect sense.

3.) If 1 & 2 are ture, how come the response time decreases when I apply
proper (IMHO) indexing?

As a database guy, I really don't care about the API, do I? I care about
the
well-being, integrity & response of the data, not how you get at it. Good
n-tier design (that thing that everybody talks about but nobody actually
does) requires (REQUIRES!) that the data store be totally ignorant of how
I
access it. I should get the same beautiful response whether I request
Project
Server data from MS Project using Microsoft's API, MS Excel via OLEDB/ODBC
or, good gawd a'mighty, my own custom API. I have three separate clients
who
have evaluated and rejected Project Server because they couldn't get under
the covers and understand what was going on with the data and make it fit
their own existing company data. That's not me taking, that's the guys who
write the checks. Example: try to integrate a company employee list stored
in
a separate SQL Server (or Oracle or whatever) table with the Project
global
resource list, the goal being to add a new employee to the company table
and
have it appear as a resource w/o having to enter/import/synchronize
through
Project. A perfectly reasonable business request that can't be done with a
blob & the MS API, folks. What's Project Server gonna say about ten
resources
named "John Smith"? Again, a perfectly reasonable business situation that
I
can't address if I can't get under the covers.

And I understand development cycles and backward compatibility; but I
don't
see many people riding horses to work in the morning or feeding their
Chevys
hay & oats.

Babs:

Rod is correct. The contents of the tables you see in the database as
"MSP_"
anything do not contain the active data that the system is processing.
All
of the "MSP_" data content is extracted from the binary record stored as
a
single field in the "MSP_PROJECTS" table. The "MSP_WEB_" data is the
active
data source for Project Server/PWA added features. Understand that all of
the actual project record data is stored as binary. Not all of the data
comprising a project is available through the database tables. Much
remains
accessible only through the Project Object Model.

The 2007 approach is a step in the right direction and toward your
approval
as it promises to give us a fully functional API. I suppose we could get
into a debate about logical database design versus physical database
design,
and how normalization may be the goal for the former and not necessarily
the
latter, but we can't put it into the context in which the Project product
team is operating in. Without knowing what the development constraints
these
folks are facing, it's difficult for me to be too hyper-critical. Keep in
mind that Microsoft has a habit of trying to make everything backward
compatible, and at a certain point that makes it difficult to stage a
development revolution.




in
message news:[email protected]...