Re: Are redundant fields ever appropriate?

Just a quick response before I go home...

--CELKO-- wrote:
> I find
> it a bit strange that your company does not have a property sticker, a
> serial number or some other physical label on their equipment that can
> be verified by walking over to the machinery.

*WE* do, but our customers don't necessarily. Take a look at my email domain name - ManageSoft - we do software management, the full Software Warehouse solution, <http://www.managesoft.com>.

One customer came to us recently who couldn't give us a list of the hundreds of physical *sites* they own, to say nothing of computer hardware or software they own :-). Needless to say our software set them straight very quickly - an inch thick printed complete software and hardware audit in two weeks...

> >> Ok, fair enough. Sometimes I have two relationships needing foreign
> keys in the same table though, and they can't be named the same. What
> do you do then? <<
> I do not understanda what you are saying.

Sorry, forgot to re-read my writing. I have a table which is related *twice* through different relationships to the same other table, so the same FK needs to appear twice, obviously with a different name each time.

> >> We also model Software (application) distinct from its Versions,
> Software
> Products (licensable entities that may cover versions of multiple
> application), Packages (an application packaged for distribution),
> Package Versions, and a whole bunch of data about other subsystems
> (distribution etc) ... so you can see that I simplified quite a bit.
> <<
> That is ugly ... I thik I will pass on that one.

It's all very friendly when you approach it from the Software Warehouse GUI... but the internal models are quite complicated. And that's before you look inside a package - files, directories, registry entries, icons, services, etc, etc, etc. Frightening :-).

> There are better ways of creating identifiers.

Agree with your comments about identities in that case. Sometimes a simple integer is all you can meaningfully do however.

> >> It would be nice if MS SQL Server supported reference propagation,
> but we must support MSDE and MS SQL Server 7 which don't, sigh. <<
> Sigh. Time to get the next release ...

We have all the releases, but not our customers.

> I will have to play with that, but the Configurations table is what
> joins the computer, the software and the organizational units.

No. Configurations only joins Computer with Software. OU is incidental. The computer is already joined by a FK to an OU, and the computerID is unique across all computers. A Configuration by definition is related to exactly one computer, which is related to exactly one OU, so you don't *need* the OU in the Configurations table - it's not even 3NF I don't think, certainly not 5NF. All columns must be related to "the key, the whole key, and nothing but the key, so help me Codd" :-) It's that third part we've broken by adding the OU...

--
Clifford Heath, ManageSoft Corporation