Normalizing

[Lars Brownie]

I have these 3 tables:

tblRequest - ID_request(PK), other Request fields
tblRequestHyperlink - ID_request, ID_hyperlink (Composite unique key on both
fields)
tblHyperlink - ID_hyperlink(PK), Hyperlink_value (Unique)

Now there are 3 other tables that I want to be able to attach an hyperlink
to as well. My plan is to keep only one tblHyperlink. But for the linking
table: Should I make a new table like tblRequestHyperlink for every other
table or can i (ab)use tblRequestHyperlink for this? How would I go about
this?

Thanks, Lars

 

[Lars Brownie]

...I want to be able to attach an hyperlink to as well.
should be

 

[bcap]

Create additional tables. It'll be much simpler in the long run.

Why does tblHyperlink have an "ID_hyperlink" PK when the Hyperlink_value
itself is unique? (Unless, of course, it's type is Memo, which would be a
reasonable choice given how long URL's can be)

 

[Lars Brownie]

Thanks. As for the ID_Hyperlink PK, I'm not sure. I just always make the
ID_number (autonumber) the primary key in the main table.

Do you mean I should make Hyperlink_value the PK, and a secundary unique
index on ID_hyperlink or no index at all on ID_hyperlink?

Lars

 

[Douglas J. Steele]

If ID_hyperlink is an AutoNumber field and you're not going to use it as the
primary key, then there's really no point in having it as a field in the
table.

While you could use the hyperlink itself as the primary key, in the long run
you may be better off using the AutoNumber, since I'd suspect that the
average length of the hyperlink field will be considerably more than the 4
bytes for an AutoNumber.

 

[test]

No, I mean I wouldn't have the ID field at all. If the hyperlink is unique
then why do you need another field?

I will admit, though, that I almost invariably use SQL Server back-ends
rather than Access, where restrictions on the size of a text (varchar) field
(column) and on the overall size of the database are much less onerous than
they are in Access.

 

[bcap]

Oops, apologies for the change of name, using a different computer...

 

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

MS Access doesnt' support normalization.. because the query engine is
too flaky.

If you really want to have a realible database operation-- and plan
storage effectively-- start with using the optimal datatypes.
This should always be rule # 1 for any application such as what you've
described.

JET doesn't support anything but unicode-- so your characters are
always _TWICE_ as big as they should be.

In other words-- move to SQL Server.

 

[bcap]

A dickhead writes:

MS Access doesnt' support normalization.. because the query engine is
too flaky.

If you really want to have a realible database operation-- and plan
storage effectively-- start with using the optimal datatypes.
This should always be rule # 1 for any application such as what you've
described.

JET doesn't support anything but unicode-- so your characters are
always _TWICE_ as big as they should be.

In other words-- move to SQL Server.

 

[Bob Larson]

Don't worry MISS Aaron knows nothing of what SHE talks about. She has no
experience with SQL Server or Access but has a grind with Microsoft because
he was arrested for defacing their lawns.

 

[Troll Chaser]

Once again, you are totally wrong. JET supports normalization. Perhaps you
should move to the SQL-Server newsgroups where you can be just as wrong as
you are here.


MS Access doesnt' support normalization.. because the query engine is
too flaky.

If you really want to have a realible database operation-- and plan
storage effectively-- start with using the optimal datatypes.
This should always be rule # 1 for any application such as what you've
described.

JET doesn't support anything but unicode-- so your characters are
always _TWICE_ as big as they should be.

In other words-- move to SQL Server.

 

[Lars Brownie]

I will admit, though, that I almost invariably use SQL Server back-ends
rather than Access, where restrictions on the size of a text (varchar)
field (column) and on the overall size of the database are much less
onerous than they are in Access.

That is the reason for the ID_hyperlink field.

Lars

 

[Lars Brownie]

While you could use the hyperlink itself as the primary key, in the long
run you may be better off using the AutoNumber, since I'd suspect that the
average length of the hyperlink field will be considerably more than the 4
bytes for an AutoNumber.

Yes, that is the reason for having the ID_hyperlink field and I also need a
unique index on Hyperlink_value to prevent users from entering duplicate
records.

Lars

 

[Larry Linson]

MS Access doesnt' support normalization..
because the query engine is too flaky.

Mr. Kempf is, perhaps, deliberately obscuring the facts. The default Jet
and ACE database engines certainly support normalization, though they do not
require it. (That is identical to the situation in MS SQL Server, Mr.
Kempf's recommendation.) Access itself does not "support normalization"
because it is the user interface and application develpment part... it
relies on a database engine to manipulate the data.

If you really want to have a realible database
operation-- and plan storage effectively-- start
with using the optimal datatypes. This should
always be rule # 1 for any application such as
what you've described.

With no definition of what Mr. Kempf believes "optimal datatypes" to be,
this part of his response is meaningless and useless.

JET doesn't support anything but unicode --
so your characters are always _TWICE_ as
big as they should be.

Mr. Kempf knows, but does not bother to state, that Unicode compression is
"ON" in Access databases by default, so unless you deliberately go in and
turn it "OFF" or unless you are using a character set that requires all
16-bits of Unicode, and English character sets do not, then you pay very
little penalty for the privilege of having Unicode available to you.

In other words-- move to SQL Server.

Mr. Kempf has a standard recommendation -- you have just read it. There are
environments in which it would be a proper recommendation; there are other
environments in which it would not be a good idea.

Larry Linson
Microsoft Office Access MVP

 

[bcap]

Yes, that is the reason for having the ID_hyperlink field and I also need
a unique index on Hyperlink_value to prevent users from entering duplicate
records.

Lars

Which would be achieved by having the hyperlink value as the PK...

 

[Lars Brownie]

Which would be achieved by having the hyperlink value as the PK...

I don't want to store the full hyperlink values twice. I want to link on the
ID_hyperlink. Earlier I did some testing on a 500000 record table. Using 1
numeric field results in a 17 MB mdb and 3 50-character fields results in a
85 MB mdb.

Lars

 

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

if you have that many records, you need to utilize SQL Server

sorry

but the datatypes-- you have a LOT more control over them in a 'real
database' like SQL Server

 

[bcap]

What's the problem with an 85Mb database?

I don't want to store the full hyperlink values twice. I want to link on
the ID_hyperlink. Earlier I did some testing on a 500000 record table.
Using 1 numeric field results in a 17 MB mdb and 3 50-character fields
results in a 85 MB mdb.

Lars

 

[Lars Brownie]

What's the problem with an 85Mb database?

This was an example. If I'd choose long alpha field as natural keys for
several other fields my mdb would grow much faster in comparison to using a
Long.

I assume smaller mdb's will result in quicker opening time and quicker
loading of the tables and even reducing corruption risks. I expect queries
to be a lot faster when they only have to deal with numbers in stead of
large natural keys.

Lars

 

[BruceM]

Referring to an earlier posting, I don't think the suggestion is to store
the hyperlink twice (once as a hyperlink field and again as a PK, but rather
there was some discussion about using the hyperlink *as* the PK. I think
there could be some problems using a memo field or hyperlink field as the PK
in that an index on a long field could slow down performance (as I
understand). Also, long URLs may include two that are the same for the
first 255 characters. Indexing occurs only on the first 255 characters, so
I think this means the value would not be allowed as a unique index.