Normalizing

[Lars Brownie]

Apart from the hyperlink value exceeding the 255 characters tip, I'm not
sure I get the difference between a PK and a secondary unique index.

What's the difference between:

ID_hyperlink (PK)
Hyperlink_value (Sec unique index)

and

Hyperlink_value (PK)
ID_hyperlink (Sec unique index)

?

Why would the second one be better? It seems more logical to have the
ID_hyperlink as PK.

If you use the Hyperlink_value field to link to another table, then you'd
have to store the Hyperlink_value in the other table as well.

Lars

 

[a a r o n _ k e m p f]

WRONG

if you want faster databases.. move to SQL Server and enjoy the world
of 'indexes'.

MS Access just doesn't work with that many records

 

[a a r o n _ k e m p f]

re:
Indexing occurs only on the first 255 characters, so
I think this means the value would not be allowed as a unique index.

wow. Sounds to me like a limit in JET.
Did I mention that SQL Server doesn't have a limit like this?

 

[BruceM]

Apart from the hyperlink value exceeding the 255 characters tip, I'm not
sure I get the difference between a PK and a secondary unique index.

What's the difference between:

ID_hyperlink (PK)
Hyperlink_value (Sec unique index)

and

Hyperlink_value (PK)
ID_hyperlink (Sec unique index)

?

Why would the second one be better? It seems more logical to have the
ID_hyperlink as PK.

I did not say the second one would be better. I would have suggested the
first one, not the second one. If you are using the hyperlink value as the
PK there is no need for another number to be stored. However, if you are
using the hyperlink field as a PK and as a linking field, and if the URL
changes, your links are no good. I think you could specify cascading
updates for the linked fields, but I tend to avoid that situation, so I
don't know what caveats may apply. If you use an autonumber (or other
unique number) field as the PK, and if the URL changes, the linked fields
are still good.

I'm not sure I would index a hyperlink or memo field unless I expected to
sort or search often by that field. Even in that case I would probably
avoid a unique index (indexes can allow duplicates).

 

[BruceM]

Even if it's possible it is not necessarily a good idea. If you think it's
a good idea, and if you have a plan for how to update the linked tables when
the URL changes, provide details and an explanation of why this is the best
approach. If as usual you have nothing to add you should go away.

re:
Indexing occurs only on the first 255 characters, so
I think this means the value would not be allowed as a unique index.

wow. Sounds to me like a limit in JET.
Did I mention that SQL Server doesn't have a limit like this?

 

[BruceM]

Access works just fine with many records, and with a db of that size. If
you are having difficulties you are doing something wrong.

WRONG

if you want faster databases.. move to SQL Server and enjoy the world
of 'indexes'.

MS Access just doesn't work with that many records

 

[a a r o n _ k e m p f]

bull crap kid

Access doesn't scale to more than a single record.

and DAO is a peice of crap-- any DAL that makes you clean up variables
isn't worth it

 

[Lars Brownie]

Thanks Bruce. I was a bit confused.

Maybe in the forms at the hyperlink's beforeupdate event I'll run a chech to
see if the hyperlink value already exists. I can't estimate how often
hyperlink values will be chosen more than once.

Lars

 

[BruceM]

It does if you know how to use it. Since you do not you should not post
replies here.

bull crap kid

Access doesn't scale to more than a single record.

and DAO is a peice of crap-- any DAL that makes you clean up variables
isn't worth it

 

[bcap]

Agreed, as mentioned earlier if the field is a memo then it's not a good
idea to use it as a PK.

 

[Douglas J. Steele]

In fact, I don't believe it's possible to use a memo field in an index.

 

[bcap]

IIRC I did try it in response to this question, and I think you *can* use a
memo field as a PK. It surprised me too!

 

[Douglas J. Steele]

Interesting. I still tend to do most of my development in Access 97, and the
help file there (if you search on "Memo fields, vs. Text fields") explicitly
states "Memo fields can't be indexed or sorted."

 

[a a r o n _ k e m p f]

yah.. well serves you right for using an obsolete database

 

[a a r o n _ k e m p f]

stop following me around, stalkr

 

[a a r o n _ k e m p f]

wow stalker, leave me alone