Re: Dreaming About Redesigning SQL
Date: Sat, 25 Oct 2003 00:52:14 -0400
Message-ID: <3522109.1067057534_at_dbforums.com>
Originally posted by andrewst
> You could have:
> 1) Uniqueness of key value (always)
> salary is a number between 1 and 99999 with up to 2 d.p.
> status_code must be in list ('A','B','C')
> if status = 'X' then end_date is not null
> 4) Database constraints e.g.
> start_date of assignment must be between start_date and end_date
> of project
>
> In general, though, a table with no parent and no child is of little
> use. It must contain information that has no relevance to any other
> information in the database. Can you give an example or two of such
> a table?
>
> Integrity is not ALL about foreign keys. It means having sensible,
> valid data in the table. Consider this row in an imaginary employee
> table where no constraints are defined:
>
> Emp ID: 123
> Name: (null)
> Sex: J
Date Hired: 01-JAN-1066 OK. Thanks. I'm learning a thing or two. I wasn't previously sure if the integrity constraints embedded in SQL tables were solely concerned with maintaining pointers for cascading deletes and whatnot.
In my experience applications have layers - although some are extremely flaky while others are more sophisticated.
I'm working on developing a 'development framework' to allow people (users) to develop applications to run on Pick-based systems. There are parameters and code modules that make up the rules to be applied in the different layers.
It would be the responsibility of the input validation layer to reject the erroneous data in your example. In a browser-based setting, for example, some of the invalid data would be rejected by JavaScript functions in the browser (although it would again, of course, be validated more rigorously with all of the other data on the application server when it gets the name/value pairs from the HTTP post). It would be bad to allow it into the 'working storage' of the application software - let alone the database.
Going back further to the numbered list of integrity constraints you gave - we don't have to worry about uniqueness of key value because Pick simply doesn't allow duplicate keys. The 'domain', 'row' and 'database' constraints that follow are, again, things that must be handled in the input validation layer.
A table without a parent or child might well be of little use in an SQLrelational database. A Pick item though can be the equivalent of a relational 'view' (I hope I'm using the correct terminology) - with the important difference that there are absolutely no 'pointers' needed to maintain the relationships between the data that make up the 'view'.
Regards
Mike
-- Posted via http://dbforums.comReceived on Sat Oct 25 2003 - 06:52:14 CEST