semantics of foreign keys: domain specific inequality?
Date: 30 Dec 2006 23:16:35 -0800
Message-ID: <1167549395.437268.47660_at_a3g2000cwd.googlegroups.com>
I'm trying to figure out how to deal with the relative importance of tables involved in a foreign-key relationship. My actual problem is one of generating HTML pages directly from database schemas (think of ruby-on-rails, django, etc.), and doing it in a way that makes sense for non-technical users (when they have the ability to declare the database schema).
Consider two sets of tables:
=>school: (school-name, description)
=>student: (student-name, description, [school-name])
school and student are tables, both have names and descriptions, student has a foreign key to school which indicates that each student has only one school but a school may have many students.
=>unix-command: (command-name, description)
=>unix-command-parameter: (parameter-name, description, [command-name])
unix-command and unix-command-parameters are two tables, each has a name and a description. unix-command-parameter has a foreign key to unix-command which indicates that a command may have many parameters but a parameter and its description are associated with only one command. (think of "grep" as a command and list of grep parameters and descriptions can be obtained by "grep --help")
Both sets of tables have the same basic structure and the exact same foreign key relation ship (one-to-many). However, while both 'school' and 'student' are important entities seperately, 'unix-command-parameter' wouldn't make any sense without 'unix-command.'
>From a software-engineering perspective, if I was making an web-application to deal with these entities, I would probably make a web page for school and one for students. For the second set, I would certainly make a web-page for unix-commands, but unix-command-parameter would only exist within the unix-command page and not as a seperate entity.
(I know the example is a bit hand-wavey but I hope you get the gist of it .... and ignore the apropos command :))
Programming languages allow things like data-hiding, utility functions which only exist in the scope of another function. Relational algebra makes all tables first-class (although I understand the issue of data-hiding is completely orthognal to relational algebra). The problem gets worse when when we go beyond one-to-many relationships to many-to-many...a large number of 'look-up' tables don't need to be seen or declared by the end-users.
After all that, my question is, how can one deal with situations where some database entities need to be 'second-class citizens.' Is there any published material related to this problem? Is it enough hide a second-class citizen in all cases except when it is being accessed (read, deleted, updated, inserted into) as part of its related first-class citizen?
(I would prefer any references that speak in terms of the foundations of databases, not necessarily specific features available in any database) Received on Sun Dec 31 2006 - 08:16:35 CET