J
John W. Vinson
For example: ISO country codes. Country Code determines Country Name.
Country Name determines Country Code.
Ummmm... not really.
USA = United States of America, Etats-Unis, Estados Unidos, ...
John W. Vinson [MVP]
For example: ISO country codes. Country Code determines Country Name.
Country Name determines Country Code.
Ummmm... not really.
USA = United States of America, Etats-Unis, Estados Unidos, ...
John W. Vinson [MVP]
Yes, I have a country table like that, where the relationship is defined
by an external standards body. (That is, the relationship between the
2chr and 3chr ISO country codes: we use localised country names)
But in general, my type tables are user defined, and I need to be able
to handle the fact that countries come and go, change name, double
up, split, share names, use multiple names etc.
What do you do when a country changes name? Just show all the
case records using the new name? I may not be able to do that for
4-10years: all the old contracts need to roll out of the system. In the
mean time, all of the new contracts need to use the new country
name.
Then when all the old contracts are gone, they still exist in the history
tables. My reports will need to group by country code, but display
country name.
It's easy to start out with multiple unique columns, but after 10 years,
most of the uniqueness seems to leak out.
(david)
In a well designed system, there will only be one unique index
on each table :~).
In a not-badly-designed real system, there will probably be only
a few tables that have multiple unique indexes.
You have created a country table with two unique keys, so that
you can identify which "country" to assign to a "contract" based
on "start date". (This is a general example of a concept).
I don't need to a assign a country to a contract based on start
date: I already have my countries assigned to contracts. All I
need is a table to hold the country information, based on a unique
key.
There are situations where it makes sense to have duplicate
unique keys.
One such situation arises from "not fully relational" data storage
mechanisms, which impose restrictions or performance penalties
on updating across multiple tables.
We know that most of the designs that put multiple unique keys
into a table, like unique keys on person name, SSN or country
name, are bad designs, which will have to be modified it the
system stays in use.
We also know that most m:n designs are an incredible amount
of work to use effectively, and that a design with multiple m:n
tables is probably a bad design, which will be more work than
it is worth.
Which leads back to where I started:
5th Normal form would be bad design in Access, or
Access would be a bad design for 5th Normal form.
That is a specific limitation.
I have a very bad impression and a low opinion of what passes for
"good design" in the Access world, but if what you say is true then
that's a pretty damning verdict on that product.
Probably a misstatement, though. A single-column lookup table where
the sole column is the primary key would be in 5NF and that's good
design even in Access land ;-)
Jamie.
--
David said:Absolutely. In general, ANY schema in at least 2NF could also be in
5NF. So the assertion about 5NF being a "specific limitation" is
almost inconceivable and makes about as much sense as the original
claim. But that's about as far as I want to go with the topic.
Anything that involves MDB tends to leave me feeling depressed and
angry :-<.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
should be.
I'm not sure what you mean by "not fully relational" in this
You can't justify sweeping statements of that kind with one o
or two alleged "bad" examples.
5NF. So the assertion about 5NF being a "specific limitation" is
almost inconceivable and makes about as much sense as the original
5NF refers specifically to situations where a circular relationship
is normalised into 3 (or more) tables.
How can I suggest what keys(s) should be used? None
of the suggested examples are things which should have
unique constraints.
You claim to have many examples which should have
multiple unique keys. Are these examples secrets?
Some data storage mechanisms impose a distinction between
a "view" and a "table", where a view may not be updateable,
or only some fields in a view may be updateable. This sometimes
forces you to do updates on "tables" that are different to your
"views". In doing so, you may choose to use a different index.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.