Access 2010 beta - Publish to Sharepoint
Bob Alston
2007 format in order to be able to see the share to Sharepoint option
and thereby get to the validation routine to see if it is compatible
with Sharepoint. It wasn't.
Three types of errors:
Fields with characters not allowed (: ? $ ........)
Relationships defined in the normal access method
"http://www.microsoft.com/office/2010/en/download-office-professional-plus/default.aspx"
Cannot use composite indexes
Anyone who has been testing in the earlier test have suggestions for how
to most easily fix these?
IN the compatible issues table result, the field "Issue Type ID" has
coded hotlinks such as AccWeb10701 but the hot link fails
Bob
Salad
> Ok I got the Access 2010 beta. found I must have the database in Access
> 2007 format in order to be able to see the share to Sharepoint option
> and thereby get to the validation routine to see if it is compatible
> with Sharepoint. It wasn't.
Thanks Bob for posting the Offic 2010 Dl site.
Could you provide further clafification on your statement above. What
do you mean...compatible with sharepoint? Or see the share?
>
> Three types of errors:
>
> Fields with characters not allowed (: ? $ ........)
That seems fine. One can use those chars in a label.
>
> Relationships defined in the normal access method
> "http://www.microsoft.com/office/2010/en/download-office-professional-plus/default.aspx"
Could you clarify?
> Cannot use composite indexes
>
When is there a need for a composite index?
Bob Alston
> Bob Alston wrote:
>
>> Ok I got the Access 2010 beta. found I must have the database in
>> Access 2007 format in order to be able to see the share to Sharepoint
>> option and thereby get to the validation routine to see if it is
>> compatible with Sharepoint. It wasn't.
>
> Thanks Bob for posting the Offic 2010 Dl site.
>
> Could you provide further clafification on your statement above. What
> do you mean...compatible with sharepoint? Or see the share?
>
To publish an Access database to Sharepoint, there are requirements that
must be met.
One requirement that the database be in Access 2007 format.
Another is that certain things are not allowed, such as the items I
mentioned in my original posting.
HTH
Bob
Salad
> Salad wrote:
>
>> Bob Alston wrote:
>>
>>> Ok I got the Access 2010 beta. found I must have the database in
>>> Access 2007 format in order to be able to see the share to Sharepoint
>>> option and thereby get to the validation routine to see if it is
>>> compatible with Sharepoint. It wasn't.
>>
>>
>> Thanks Bob for posting the Offic 2010 Dl site.
>>
>> Could you provide further clafification on your statement above. What
>> do you mean...compatible with sharepoint? Or see the share?
>>
>
> To publish an Access database to Sharepoint, there are requirements that
> must be met.
>
> One requirement that the database be in Access 2007 format.
Would that be a problem? One can link to a SharePoint table with A2003.
>
> Another is that certain things are not allowed, such as the items I
> mentioned in my original posting.
But the first and third appeared to be extremely minor. I didn't
understand the relationships issue you mentioned. Are the relationships
set in the Sharepoint site or in the Access app or neither? Is there a
new way to define relationships?
>
> HTH
>
> Bob
Bob Alston
have.
In using Access 2010 beta, I had to have the database in Access 2007
format for the "publish to Sharepoint" option to become active. I have
no idea how this works in older versions of Access.
Bob
Salad
But isn't that A-OK? We're talking about publishing an app to the web.
That would include forms and reports. Not just back end data.
Albert mentioned there'd be some adjustments. I guess you came across some.
David W. Fenton
news:55hNm.30444$X01....@newsfe07.iad:
> One requirement that the database be in Access 2007 format.
That is, ACCDB. MDB is also an Access 2007 format.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton
news:SYqdnT0fcu8aBJjW...@earthlink.com:
> When is there a need for a composite index?
N:N join table with two columns, where each is the PK of the tables
being joined -- unique PK on two fields.
This is essential for any proper N:N join table.
Albert D. Kallal
news:pYfNm.44381$de6....@newsfe21.iad...
> Ok I got the Access 2010 beta. found I must have the database in Access
> 2007 format in order to be able to see the share to Sharepoint option and
> thereby get to the validation routine to see if it is compatible with
> Sharepoint. It wasn't.
We don't see a 2010 format here. So, taking a BIG stab at this, obviously
the 2007 and 2010 format will be the same or it not been changed for beta. I
see 2003, and then I see the current format. So, as it stands now, the
"latest" format is 2007...
I think in our computer industry, since about near day one, I quite sure
that new features as a general rule always relied on the newest file
formats.
I mean, you can't use older formats of access files and use the multi-value
fields that we received in 2007. So, this again is going to be no different,
then what, about the last 20 years of our computer industry?
So, for excel, if you use new features, they rarely will be backwards
available in previous versions.
so, for Word, if you use new features, they rarely will be backwards
available in pervious venison.
And, dbaseII could not open dbaseIII files...and...(well, you get the idea
here).
So for access, if you use new features, then one would not be surprised that
you need to use the latest format. I would expect this trend follows the
experience we seen for every product in the last 20 years of our industry...
>
> Three types of errors:
>
> Fields with characters not allowed (: ? $ ........)
I have never ever used any strange characters in fields for at least 10
years now. I think once used #, but really, no spaces, and no symbols is
something that been preached by long time regulars here likely since the
inception of this newsgroup. Now you learning why we preach such things. You
do realize that even sql server does not support spaces in field names?
So, no junk, and no spaces in field names is a great programming practice
that served me very well in the computer industry.
> Anyone who has been testing in the earlier test have suggestions for how
> to most easily fix these?
Well, for field names, simply get rid of the garbage characters you have,
and while you at it, adopt a policy of no spaces in field names.
>
> Cannot use composite indexes
Correct. You can certainly set/create an index on each column. From a
performance point of view, this should not hurt in most cases. However, if
you are using two columns, and an unique index on that column, then you have
to forego this feature. Another workaround is to use a trigger to fill out a
column that is the concatenation of the two values and place a unique index
on this column. eg:
SalesKey = [SalesRep] & "*" [AccountNumber]
So, in the above we take the sales rep, and the account number they been
assigned to. (only one rep can be assigned to a accountnumber), the above
will create a unique key column. We used this trick for years for PC
database systems like Revelation (remember that from the 80's!), and I also
used that trick for systems like IBM's universe and d3 (tiger logic).
If you followed the other thread, there was link to limitations of
SharePoint lists.
Some suggestions:
Primary Key:
Use ID, this has been the default PK for ms-access as an autonumber column
called ID for about 17 years now. It is a good default. Is quite much a
requirement to continue this trend for tables that you plan to send to
SharePoint.
In fact, if you every up-sized anything to sql server, you also find that
using ID for the PK column works quite well.
So, keep your designs to the simple PK of ID, and for FK values, again use a
number column.
Keep in mind that you must create a blank web database and then import
objects in for the checker to really work its magic. Note that all VBA
forms etc WILL pass the web checker since they are in fact by-passed during
the checking process. The reason for this is of course forms with VBA are
permitted in these applications. You will CLEARLY see what forms are
marked/set as web as opposed to client forms (no globe icon in the nav pane
for client objects). VBA client forms are permitted and they simply don't
publish to the web (so, they don't show up in the compatibility checker at
all either...they are skipped).
Once you have web tables, then you will quickly find out that to setup
relationships, you use the lookup wizard. If you need relations diagrams,
you have to use the query builder.
Unfortunately for SharePoint tables, they could not build a relationships
window editor. (it was a feature the access team wanted, but budgets + time
means it could not make this cut). Remember, after you published, changes +
edits to table designs are permitted, but the tables now reside on
SharePoint. At least we don't have to open the table (list) on the
SharePoint site to make the changes. I mean, with any odbc server you use
with access such as MySql, ms-sql, oracle etc, you have to go the server
side to make tables changes.
In access, we can add/delete, setup indexes, and setup relations all from
the ms-access client all the while the tables are on SharePoint. So, at
least we can modify server tables from inside of ms-access and not be forced
to go to the SharePoint site. So, at least we have this client table change
ability, but unfortunately we don't have the relationships window to do this
for us....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
David W. Fenton
news:MdadnciZR77gP5jW...@earthlink.com:
> Bob Alston wrote:
>> One requirement that the database be in Access 2007 format.
>
> Would that be a problem? One can link to a SharePoint table with
> A2003.
I think you're completely missing the point. Bob was trying to test
the new publish to Sharepoint feature, where you create an ACCDB and
can publish it to Sharepoint, where it will run in the browser and
look nearly identical to the way it looks running in Access. There
was a humongous thread on this very topic last week.
Linking to Sharepoint is not even close to the same thing as the new
features being offered by A2010 in terms of Sharepoint integration.
Salad
> Salad <o...@vinegar.com> wrote in
> news:MdadnciZR77gP5jW...@earthlink.com:
>
>
>>Bob Alston wrote:
>
>
>>>One requirement that the database be in Access 2007 format.
>>
>>Would that be a problem? One can link to a SharePoint table with
>>A2003.
>
>
> I think you're completely missing the point. Bob was trying to test
> the new publish to Sharepoint feature, where you create an ACCDB and
> can publish it to Sharepoint, where it will run in the browser and
> look nearly identical to the way it looks running in Access. There
> was a humongous thread on this very topic last week.
>
> Linking to Sharepoint is not even close to the same thing as the new
> features being offered by A2010 in terms of Sharepoint integration.
>
I still don't get it. Who cares about the format being a problem if one
can publish an app to the web. Is the problem that one can't get to the
data unless it's in A2010? Either way, who cares?
David W. Fenton
news:sGnNm.45590$Wf2....@newsfe23.iad:
> Primary Key:
> Use ID, this has been the default PK for ms-access as an
> autonumber column called ID for about 17 years now. It is a good
> default. Is quite much a requirement to continue this trend for
> tables that you plan to send to SharePoint.
>
> In fact, if you every up-sized anything to sql server, you also
> find that using ID for the PK column works quite well.
>
> So, keep your designs to the simple PK of ID, and for FK values,
> again use a number column.
I think you're mixing in two different issues:
1. PK
2. indexes
So far as I understand it, because Sharepoint doesn't support
multi-field indexes it therefore cannot support composite PKs. So,
your suggestion only partway solves the problem, as it provides a
replacement PK for the multi-column key.
But it doesn't address the indexing issue -- unique indexes on
multiple columns are pretty important, don't you think?
And the example I gave in another response was of your standard N:N
join table, which is made up of two foreign keys that together make
the primary key. Adding a surrogate key is not going to help at all,
unless you're joining something to the record in the join table (in
which case, I would have argued that you should have had the
surrogate key in there to begin with, and not because of
Sharepoint!).
It seems to me that join tables are not supported in Sharepoint.
While this is not a terrible problem for where one side is a
single-column join (that can be replaced with a multi-value field),
if you're joining complex entities, you're out of luck.
I guess a table-level data macro could be used to enforce
uniqueness, but it sounds expensive in terms of processing.
David W. Fenton
news:msqdnXyZM4aOg5vW...@earthlink.com:
???
New features have been added to A2010 that enable this massive new
set of capabilities.
It is entirely dependent on new features that have been introduced
over the course of the development of A2007 and A2010:
A2007
- multi-value fields
- attachment fields
- append-only memos
- embedded macros
A2010
- table-level data macros
- web forms/reports
When you put those new A2010 features together with what was already
introduced in A2007, you get the capability to publish your Access
app to Access services on Sharepoint. But you have to have all the
parts, and the most important part (the web forms/reports) are
entirely new in A2010.
It's all of these things that enable the publishing and tight
integration with Sharepoint. It's these new features that make the
web version nearly indistinguishable from the Access version.
Albert D. Kallal
>
> So far as I understand it, because Sharepoint doesn't support
> multi-field indexes it therefore cannot support composite PKs. So,
> your suggestion only partway solves the problem, as it provides a
> replacement PK for the multi-column key.
>
> But it doesn't address the indexing issue -- unique indexes on
> multiple columns are pretty important, don't you think?
Do you mean a compound index like an index based on more then one column, or
do you mean several collumns each with an unique index?
We have the 2nd case above..
So, you can have many indexes, and they all each be set as unique. You
simply don't have indexes that are compound, or the result of more then one
column however. So, that's why I gave that possible work around.
So, to be clear:
for 2010 you can have many indexes. And as many as those columns can be set
as unique.
We just don't have a compound index ability.
Albert D. Kallal
> New features have been added to A2010 that enable this massive new
> set of capabilities.
>
> It is entirely dependent on new features that have been introduced
> over the course of the development of A2007 and A2010:
>
> A2007
> - multi-value fields
> - attachment fields
> - append-only memos
> - embedded macros
>
> A2010
> - table-level data macros
> - web forms/reports
>
Yes... (thanks for jumping in here..)....
Keep in mind, as it stands, I believe they going to keep the format the same
for 2007 to 2010. And, right now, if you open a table with triggers in
access 2007, you get an message explain that features are present that don't
allow you to update the table. So, the ace 2007 is aware of those
triggers...and will tell you!
I already tested the above. So, access 2007 (sp2) can open the current 2010
accDB files. I believe the policy and goal is things to remain as this
beyond the beta release...
In other words, if you use a table feature in 2010, access 2007 can open it,
but will inform you it can't be updated.
So, to add more confusing, I believe the goal here is to allow access 2007
to open the 2010 files. This means I don't think they going to come out with
a specific 2010 format as of this time...
And, for a database marked as web only, there not a different extension (we
have too many already!). I have no idea what happens if I open an 2010 web
app in 2007,..hold on..let me try right now....
Oh, now this is cool. I can open the accDB file. And, I even see the objects
in the nav pane.
I also get a message from 2007 warning about features not supported. The web
link given here is VERY cool:
http://msdn.microsoft.com/en-us/office/cc907897.aspx
Enjoy the above read...it interesting....
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
>
>> So far as I understand it, because Sharepoint doesn't support
>> multi-field indexes it therefore cannot support composite PKs.
>> So, your suggestion only partway solves the problem, as it
>> provides a replacement PK for the multi-column key.
>>
>> But it doesn't address the indexing issue -- unique indexes on
>> multiple columns are pretty important, don't you think?
>
> Do you mean a compound index like an index based on more then one
> column, or do you mean several collumns each with an unique index?
I mean exactly what I said, a compound index, i.e., an index with
more than one column.
> We have the 2nd case above..
>
> So, you can have many indexes, and they all each be set as unique.
> You simply don't have indexes that are compound, or the result of
> more then one column however. So, that's why I gave that possible
> work around.
But your workaround does not actually solve the problem. If the
table requires a unique index on multiple columns (as in the N:N
join table) you cannot implement it properly. If you have a unique
natural key that needs to be enforced at the engine level, you're
stuck.
> So, to be clear:
> for 2010 you can have many indexes. And as many as those columns
> can be set as unique.
>
> We just don't have a compound index ability.
And that's a major, major lack.
David W. Fenton
> In other words, if you use a table feature in 2010, access 2007
> can open it, but will inform you it can't be updated.
This is exactly the way replicated files worked, i.e., a replicated
Jet 3.5 file could be opened in A2000 but read-only.
Salad
>>for 2010 you can have many indexes. And as many as those columns
>>can be set as unique.
>>
>>We just don't have a compound index ability.
>
>
> And that's a major, major lack.
>
Dbase and FoxPro but with Access...never. I'm sure there's a reason and
a use for them but I wouldn't know where they'd be useful or why.
David W. Fenton
news:Od2dneGl3cUl7ZXW...@earthlink.com:
N:N join table, two IDs from the tables you are joining. You want a
unique index on the combination of the two columns, and, of course,
it should also be the PK.
I have dozens of such tables in my apps. Doesn't everyone?
Salad
> Salad <o...@vinegar.com> wrote in
> news:Od2dneGl3cUl7ZXW...@earthlink.com:
>
>
>>David W. Fenton wrote:
>>
>>>"Albert D. Kallal" <PleaseNOOO...@msn.com> wrote in
>>>
>>>>So, to be clear:
>>>>for 2010 you can have many indexes. And as many as those columns
>>>>can be set as unique.
>>>>
>>>>We just don't have a compound index ability.
>>>
>>>And that's a major, major lack.
>>>
>>
>>Could I ask why? I remember using compound indexes back in the
>>days of Dbase and FoxPro but with Access...never. I'm sure
>>there's a reason and a use for them but I wouldn't know where
>>they'd be useful or why.
>
>
> N:N join table, two IDs from the tables you are joining. You want a
> unique index on the combination of the two columns, and, of course,
> it should also be the PK.
>
> I have dozens of such tables in my apps. Doesn't everyone?
>
What is a N:N?
I suppose there's a use for compound indexes and if you have lots of
tables that utilize compound indexes, you have a need. I haven't found
the need but that might be due to my application logic.
James A. Fortune
wrote:
> I have dozens of such tables in my apps. Doesn't everyone?
I have plenty of junction tables. I typically have an AutoNumber PK
and two indexed Foreign Key fields. When I add new combinations to
the junction table, I use a form that checks for existing combinations
(if I even allow existing combinations to show up at all). The
indexed Foreign Key fields seem adequate for speeding up queries,
although most of the junction tables I use don't get very large. So I
don't perceive a "major, major" lack. YMMV. Here's an example:
tblTravelClubMembers
TID AutoNumber
MemberName Text
TID MemberName
1 Jane
2 Joe
3 Jill
tblDestinations
DID AutoNumber
DestinationCity Text
DID DestinationCity
1 Paris
2 Rome
3 Albuquerque
tblClubMemberHasVisited
CMHVID AutoNumber
CID Long (Foreign Key)
DID Long (Foreign Key)
CMDVID CID DID
1 1 1
2 2 1
3 1 2
4 1 3
5 2 3
qryHasBeenTo
SELECT MemberName, DestinationCity As HasBeenTo FROM
(tblTravelClubMembers INNER JOIN tblClubMemberHasVisited ON
tblTravelClubMembers.TID = tblClubMemberHasVisited.TID) INNER JOIN
tblDestinations ON tblClubMemberHasVisited.DID = tblDestinations.DID
ORDER BY tblTravelClubMembers.TID, tblDestinations.DID;
!qryHasBeenTo
MemberName HasBeenTo
Jane Paris
Jane Rome
Jane Albuquerque
Joe Paris
Joe Albuquerque
The primary keys from the related tables plus the two indexed fields
in the junction table adequately index all the joins in qryHasBeenTo.
I suppose that an AutoNumber primary key is not actually necessary in
the junction table. I see little value in using a compound primary
key for the junction table unless it is being edited directly and you
want to prevent the user from accidentally duplicating a combination.
BTW, a many-to-many relationship corresponds to the formal definition
of a Relationship in Mathematics, but I like to point out that, under
normal circumstances, the relationship is between the keys rather than
between the data. I.e., the junction table is where the actual
mathematical relationship between the keys is defined. It is not
until the query is run that the data pointed to by a key becomes
related to other data pointed to by other keys. I.e., query results,
rather than the records in a junction table, define a mathematical
relationship on the data.
James A. Fortune
CDMAP...@FortuneJames.com
My dad took pool lessons from pro player Buddy "the Rifleman" Hall.
He was chosen to play Willie Mosconi in an exhibition match of 14.1
(continuous) and lost to Willie, but routinely beat pros for cash in
nineball.
David W. Fenton
news:vM2dnUD5G6QmcJfW...@earthlink.com:
> David W. Fenton wrote:
>
>> Salad <o...@vinegar.com> wrote in
>> news:Od2dneGl3cUl7ZXW...@earthlink.com:
>>
>>
>>>David W. Fenton wrote:
>>>
>>>>"Albert D. Kallal" <PleaseNOOO...@msn.com> wrote in
>>>>
>>>>>So, to be clear:
>>>>>for 2010 you can have many indexes. And as many as those
>>>>>columns can be set as unique.
>>>>>
>>>>>We just don't have a compound index ability.
>>>>
>>>>And that's a major, major lack.
>>>>
>>>
>>>Could I ask why? I remember using compound indexes back in the
>>>days of Dbase and FoxPro but with Access...never. I'm sure
>>>there's a reason and a use for them but I wouldn't know where
>>>they'd be useful or why.
>>
>>
>> N:N join table, two IDs from the tables you are joining. You want
>> a unique index on the combination of the two columns, and, of
>> course, it should also be the PK.
>>
>> I have dozens of such tables in my apps. Doesn't everyone?
>
> What is a N:N?
Many-to-many. Standard terminology, actually, though in Access we
see the infinity symbol in its place (ASCII 236, which my newsreader
won't let me paste into this message -- it keeps coming out as an
8).
> I suppose there's a use for compound indexes and if you have lots
> of tables that utilize compound indexes, you have a need. I
> haven't found the need but that might be due to my application
> logic.
If you have many-to-many joins, you *must* have compound indexes, or
you're forcing yourself to enforce uniqueness in your application.
David W. Fenton
news:a8dd5ff2-b5de-41ac...@f10g2000vbl.googlegroups.co
m:
> I see little value in using a compound primary
> key for the junction table unless it is being edited directly and
> you want to prevent the user from accidentally duplicating a
> combination.
Why bother with RI? You can enforce it in your application?
This is a *really* old argument, and you're going to lose it. If the
entities are unique, then a standard N:N join record *must* be
unique. That is, if there can be only one record joining the two FKs
together, then you need a unique compound index. Now, if you have
other fields, such that there can be more than one record with any
pair of FK values, you've just moved the uniqueness index from a
2-column key to a 3-column key, i.e., the two FK fields plus the
third field that has to be unique. For instance, that could be a
date field, such that you have only one record joining the two
tables for any particular day.
The unique index is *required*, not for performance, but for
enforcing uniqueness at the engine level.
You never leave anything that can be enforced at engine level to the
UI level because you can't guarantee that your data is going to be
edited by your particular application. A new application might
replace yours and fail to note the restriction, with the result that
invalid data could be entered.
I am stunned that two prolific posters in the Access newsgroups
could fail to appreciate such a basic point. It's not even
debatable, in my opinion -- if any combination of fields in a table
must be unique because of the entities being modelled, you have to
enforce that with a unique index, insofar as that's possible (in
some cases it isn't, i.e., when you have to have Null fields and
default values are going to cause problems).
What this means for an Access app that is published to Sharepoint is
that the Access app has to be crippled in terms of relaxed data
integrity. The reason lack of RI was such a show-stopper in the
previous Sharepoint verion was exactly that, i.e., that you couldn't
create a Sharepoint app that would enforce the most basic aspects of
data integrity.
N:N join tables with a unique compound key on the two fields are a
basic part of that, and the lack of support for compound keys is a
major deficiency.
Salad
Ahh. I've seen M:M before. The infinity symbol would have made sense
as well.
>>I suppose there's a use for compound indexes and if you have lots
>>of tables that utilize compound indexes, you have a need. I
>>haven't found the need but that might be due to my application
>>logic.
>
>
> If you have many-to-many joins, you *must* have compound indexes, or
> you're forcing yourself to enforce uniqueness in your application.
>
I don't have any compound indexes in any of my apps. SQL seems to be
good enough to present/filter data, a form to validate data before
committing, and the need for a compound index hasn't presented itself to
me. In the first ADH book I read back in '97 or '98 I remember some
mention about N:N but the author felt there was little complelling
reason for one.
My goal in an app is to do something that works and works quickly and
accurately. If it does, fine. If it doesn't, not fine. In programming
there oftentimes is more than one way to skin a cat...we see it all the
time in posts here. So if I don't use them and my app works...fine. If
you use them and your app works...fine. The goal was met.
David W. Fenton
news:QIudndKDdaiPE5HW...@earthlink.com:
> In the first ADH book I read back in '97 or '98 I remember some
> mention about N:N but the author felt there was little complelling
> reason for one.
I don't believe that you understood that correctly.
Common N:N:
You have a table of people.
You have a table of categories (e.g., Vendor, Customer, etc.).
Each person can be in more than one category, so you have a table in
between the people table and the categories table, with two columns,
PersonID and CategoryID. That's your standard N:N join table, and it
needs to have a unique index on the combination of PersonID and
CategoryID.
Now, the world will not end if you end up with a category listed
twice (or even 100 times) for 1 person, but it's nonsense data, as
the duplicate records are completely redundant.
In other N:N cases, it's more important that the uniqueness be
enforced, and where that's required, Sharepoint requires you to do
it in the application, which is a lot more work than having the
database engine enforce such a simple requirement.
And then there's the case of unique compound indexes that prevent
the insertion of duplicate records in a non-join table (usually on
the natural key when you've chosen to use a surrogate key as the
PK). An example might be a list of companies, where you have a
unique index on company name and city/state/zip (or some subset of
those 4 fields). Now, you might choose to model that as a company
table and then a related table of addresses, but that's not always
the optimal way to store the data (even if it would be more
normalized in an abstract sense). But if your data model works
better with a flatter structure, you need that unique index.
There really isn't any case that can be made for not having compound
unique indexes except one that ignores all the best principles of
database design. Saying you'll do it in the app is a complete
cop-out -- that's bad design. You do as much in the database engine
as possible in order to insure that there is no possibility that bad
data can get inserted into your database.
This is a principle that should not be in dispute.
And arguing that it doesn't matter that you don't have compound
indexes is abandoning that principle, and arguing for bad
schema/application design. Sharepoint is forcing that on users, but
that's not a good thing. It's not even a neutral thing -- it's very
serious deficiency.
Granted all that, the lack of RI Sharepoint before 2010 is a much
worse lack. But when they fix things like the lack of RI, they
should be fixing the other deficiencies, like the lack of compound
indexes.
It may be "good enough" that doesn't make it GOOD.
James A. Fortune
wrote:
> "James A. Fortune" <CDMAPos...@FortuneJames.com> wrote innews:a8dd5ff2-b5de-41ac...@f10g2000vbl.googlegroups.co
> m:
>
> > I see little value in using a compound primary
> > key for the junction table unless it is being edited directly and
> > you want to prevent the user from accidentally duplicating a
> > combination.
>
> Why bother with RI? You can enforce it in your application?
You seem to have missed the point of my argument. I was talking only
about junction tables and the seriousness of the need for RI in
junction tables alone. We all agree that RI enforced at the table
level is wonderful. Nearly every engineering lab I took stressed the
importance of doing everything possible to avoid bad data and to
ensure that the raw data gathered made sense. The ability of having
compound keys is also good. It was using the junction table as a
prime example of the need for a compound key that was bad.
> The unique index is *required*, not for performance, but for
> enforcing uniqueness at the engine level.
>
> You never leave anything that can be enforced at engine level to the
> UI level because you can't guarantee that your data is going to be
> edited by your particular application. A new application might
> replace yours and fail to note the restriction, with the result that
> invalid data could be entered.
Your proposed disaster scenario is implausible in the case of junction
tables.
> What this means for an Access app that is published to Sharepoint is
> that the Access app has to be crippled in terms of relaxed data
> integrity. The reason lack of RI was such a show-stopper in the
> previous Sharepoint verion [sic] was exactly that, i.e., that you couldn't
> create a Sharepoint app that would enforce the most basic aspects of
> data integrity.
AFAICT, no one here disputes that point.
>
> N:N join tables with a unique compound key on the two fields are a
> basic part of that, and the lack of support for compound keys is a
> major deficiency.
While not a major deficiency for junction tables, I agree that support
for compound keys would be a good thing to be added to SharePoint,
along with RI support.
James A. Fortune
CDMAP...@FortuneJames.com
David W. Fenton
m:
> On Nov 24, 8:34 pm, "David W. Fenton"
> <XXXuse...@dfenton.com.invalid> wrote:
>> "James A. Fortune" <CDMAPos...@FortuneJames.com> wrote
>> innews:a8dd5ff2-b5de-41ac...@f10g2000vbl.googlegrou
>> ps.co m:
>>
>> > I see little value in using a compound primary
>> > key for the junction table unless it is being edited directly
>> > and you want to prevent the user from accidentally duplicating
>> > a combination.
>>
>> Why bother with RI? You can enforce it in your application?
>
> You seem to have missed the point of my argument. I was talking
> only about junction tables and the seriousness of the need for RI
> in junction tables alone.
I wasn't talking about RI in regard to junction tables, and I never
was at any point.
> We all agree that RI enforced at the table
> level is wonderful. Nearly every engineering lab I took stressed
> the importance of doing everything possible to avoid bad data and
> to ensure that the raw data gathered made sense. The ability of
> having compound keys is also good. It was using the junction
> table as a prime example of the need for a compound key that was
> bad.
I wasn't using it as the "prime" example. It was just the easiest to
explain. Losing multi-column RI would not bother me, as I don't
compound keys for RI. But losing the compound index would be a
severe problem for junction tables, as well for all the multi-column
natural unique keys (which I never use for RI, but which still need
uniqueness enforced).
>> The unique index is *required*, not for performance, but for
>> enforcing uniqueness at the engine level.
>>
>> You never leave anything that can be enforced at engine level to
>> the UI level because you can't guarantee that your data is going
>> to be edited by your particular application. A new application
>> might replace yours and fail to note the restriction, with the
>> result that invalid data could be entered.
>
> Your proposed disaster scenario is implausible in the case of
> junction tables.
Eh? What disaster scenario are you talking about?
[]
>> N:N join tables with a unique compound key on the two fields are
>> a basic part of that, and the lack of support for compound keys
>> is a major deficiency.
>
> While not a major deficiency for junction tables, I agree that
> support for compound keys would be a good thing to be added to
> SharePoint, along with RI support.
It *is* a major deficiency for junction tables, precisely because
most of them require a unique compound index on the combination of
keys being joined. There is no getting around that as proper schema
design.
James A. Fortune
On Nov 28, 11:32 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
> "James A. Fortune" <CDMAPos...@FortuneJames.com> wrote innews:1e61f177-ffff-43dc...@m16g2000yqc.googlegroups.co
> m:
>
>
>
> > On Nov 24, 8:34 pm, "David W. Fenton"
> > <XXXuse...@dfenton.com.invalid> wrote:
> >> "James A. Fortune" <CDMAPos...@FortuneJames.com> wrote
> >> innews:a8dd5ff2-b5de-41ac...@f10g2000vbl.googlegrou
> >> ps.co m:
>
> >> > I see little value in using a compound primary
> >> > key for the junction table unless it is being edited directly
> >> > and you want to prevent the user from accidentally duplicating
> >> > a combination.
>
> >> Why bother with RI? You can enforce it in your application?
>
> > You seem to have missed the point of my argument. I was talking
> > only about junction tables and the seriousness of the need for RI
> > in junction tables alone.
>
> I wasn't talking about RI in regard to junction tables, and I never
> was at any point.
Then maybe I misunderstood what you meant by an N:N join table. Did
you not mean a junction table?
>
> > We all agree that RI enforced at the table
> > level is wonderful. Nearly every engineering lab I took stressed
> > the importance of doing everything possible to avoid bad data and
> > to ensure that the raw data gathered made sense. The ability of
> > having compound keys is also good. It was using the junction
> > table as a prime example of the need for a compound key that was
> > bad.
>
> I wasn't using it as the "prime" example. It was just the easiest to
> explain. Losing multi-column RI would not bother me, as I don't
> compound keys for RI. But losing the compound index would be a
> severe problem for junction tables, as well for all the multi-column
> natural unique keys (which I never use for RI, but which still need
> uniqueness enforced).
Multicolumn indices are indeed convenient when natural keys are used,
but natural keys don't seem to be as "natural" for junction tables.
>
> >> The unique index is *required*, not for performance, but for
> >> enforcing uniqueness at the engine level.
>
> >> You never leave anything that can be enforced at engine level to
> >> the UI level because you can't guarantee that your data is going
> >> to be edited by your particular application. A new application
> >> might replace yours and fail to note the restriction, with the
> >> result that invalid data could be entered.
>
> > Your proposed disaster scenario is implausible in the case of
> > junction tables.
>
> Eh? What disaster scenario are you talking about?
The disaster where some database person, who knows what a junction
table is, creates a new application based on my particular application
then either adds duplicate entries (foreign keys) directly to the
table by hand or creates a form that doesn't check for existing
combinations when adding new combinations.
>
> []
>
> >> N:N join tables with a unique compound key on the two fields are
> >> a basic part of that, and the lack of support for compound keys
> >> is a major deficiency.
>
> > While not a major deficiency for junction tables, I agree that
> > support for compound keys would be a good thing to be added to
> > SharePoint, along with RI support.
>
> It *is* a major deficiency for junction tables, precisely because
> most of them require a unique compound index on the combination of
> keys being joined. There is no getting around that as proper schema
> design.
It wasn't that difficult to find a workaround. It's true almost by
definition that a workaround is not is good as the best way. How
"major" the deficiency is depends on how bad it is to use the
workaround. It is apparently frightful in your world not to be able
to conform to the absolute best practices all the time. For me,
getting to the point where I use the best of everything I know and
incorporate it into my databases is an ongoing process. So someone
like myself who has not yet reached database programming perfection is
not as shocked by such deviations from the best way. I have still not
added many CHECK constraints to tables that I know should have them
because, frankly, it's not that urgent. I think your pedantry is to
be admired, but we're not talking about a "2012" crisis here.
James A. Fortune
CDMAP...@FortuneJames.com
Historical books I'm currently reading:
After Tamerlane
The House of Medici
David W. Fenton
:
> On Nov 28, 11:32 pm, "David W. Fenton"
> <XXXuse...@dfenton.com.invalid> wrote:
>> "James A. Fortune" <CDMAPos...@FortuneJames.com> wrote
>> innews:1e61f177-ffff-43dc...@m16g2000yqc.googlegrou
>> ps.co m:
>> > On Nov 24, 8:34 pm, "David W. Fenton"
>> > <XXXuse...@dfenton.com.invalid> wrote:
>> >> "James A. Fortune" <CDMAPos...@FortuneJames.com> wrote
>> >> innews:a8dd5ff2-b5de-41ac...@f10g2000vbl.googleg
>> >> rou ps.co m:
>>
>> >> > I see little value in using a compound primary
>> >> > key for the junction table unless it is being edited
>> >> > directly and you want to prevent the user from accidentally
>> >> > duplicating a combination.
>>
>> >> Why bother with RI? You can enforce it in your application?
>>
>> > You seem to have missed the point of my argument. I was
>> > talking only about junction tables and the seriousness of the
>> > need for RI in junction tables alone.
>>
>> I wasn't talking about RI in regard to junction tables, and I
>> never was at any point.
>
> Then maybe I misunderstood what you meant by an N:N join table.
> Did you not mean a junction table?
Yes, but the issue was not RI, but the required compound index.
And it *is* required for most junction tables, as multiple join
records are logically nonsensical in most junction tables.
>> > We all agree that RI enforced at the table
>> > level is wonderful. Nearly every engineering lab I took
>> > stressed the importance of doing everything possible to avoid
>> > bad data and to ensure that the raw data gathered made sense.
>> > The ability of having compound keys is also good. It was using
>> > the junction table as a prime example of the need for a
>> > compound key that was bad.
>>
>> I wasn't using it as the "prime" example. It was just the easiest
>> to explain. Losing multi-column RI would not bother me, as I
>> don't compound keys for RI. But losing the compound index would
>> be a severe problem for junction tables, as well for all the
>> multi-column natural unique keys (which I never use for RI, but
>> which still need uniqueness enforced).
>
> Multicolumn indices are indeed convenient when natural keys are
> used, but natural keys don't seem to be as "natural" for junction
> tables.
I don't know what you'd call a join between Person and Category on
PersonID and CategoryID, natural or surrogate, but it *must* have
unique index.
As to natural keys, if there is a unique natural key (independent of
whether it is used as the PK or a surrogate key is used instead), it
has to have a unique index on it. This is something that everyone,
including those who are anti-natural key (like me) agrees on, i.e.,
that even if you don't use it as the PK, it still has to have a
unique index *if* it is unique.
This is pretty basic stuff.
>> >> The unique index is *required*, not for performance, but for
>> >> enforcing uniqueness at the engine level.
>>
>> >> You never leave anything that can be enforced at engine level
>> >> to the UI level because you can't guarantee that your data is
>> >> going to be edited by your particular application. A new
>> >> application might replace yours and fail to note the
>> >> restriction, with the result that invalid data could be
>> >> entered.
>>
>> > Your proposed disaster scenario is implausible in the case of
>> > junction tables.
>>
>> Eh? What disaster scenario are you talking about?
>
> The disaster where some database person, who knows what a junction
> table is, creates a new application based on my particular
> application then either adds duplicate entries (foreign keys)
> directly to the table by hand or creates a form that doesn't check
> for existing combinations when adding new combinations.
How is that implausible? That individual might very well be *you*,
just some time later when you've forgotten that you have to enforce
uniqueness in the UI because you can't do it at the engine level.
>> []
>>
>> >> N:N join tables with a unique compound key on the two fields
>> >> are a basic part of that, and the lack of support for compound
>> >> keys is a major deficiency.
>>
>> > While not a major deficiency for junction tables, I agree that
>> > support for compound keys would be a good thing to be added to
>> > SharePoint, along with RI support.
>>
>> It *is* a major deficiency for junction tables, precisely because
>> most of them require a unique compound index on the combination
>> of keys being joined. There is no getting around that as proper
>> schema design.
>
> It wasn't that difficult to find a workaround. It's true almost
> by definition that a workaround is not is good as the best way.
> How "major" the deficiency is depends on how bad it is to use the
> workaround. It is apparently frightful in your world not to be
> able to conform to the absolute best practices all the time.
Well, no. But I consider uniqueness of N:N tables to be so basic as
to be a significant deficiency when not present.
Now, I've written applications for MySQL using MYISAM tables, which
means no referential integrity at all, so, yes, I've worked around
these kinds of things. But it makes for much less reliable apps. In
one case, a PHP upgrade caused the application's code to behave
differently and inserted a bunch of records with a 0 foreign key.
With proper restrictions at the database engine level, incorrect
data would not have been insertable at all.
> For me,
> getting to the point where I use the best of everything I know and
> incorporate it into my databases is an ongoing process. So
> someone like myself who has not yet reached database programming
> perfection is not as shocked by such deviations from the best way.
> I have still not added many CHECK constraints to tables that I
> know should have them because, frankly, it's not that urgent. I
> think your pedantry is to be admired, but we're not talking about
> a "2012" crisis here.
This is a common rhetorical strategy for the intellectually
dishonest, i.e., to characterise the opposing argument as having
been made in terms that are hysterical.
I understand once again why I long ago put you in my killfile. There
you shall remain.
James A. Fortune
wrote:
> This is a common rhetorical strategy for the intellectually
> dishonest, i.e., to characterise the opposing argument as having
> been made in terms that are hysterical.
You're not hysterical - you just overreacted.
>
> I understand once again why I long ago put you in my killfile. There
> you shall remain.
As you wish.
James A. Fortune
CDMAP...@FortuneJames.com