Re: Dreaming About Redesigning SQL

Originally posted by Mike Preece

> andrewst <member14183_at_dbforums.com> wrote in message
> news:<3518972.1066987317_at_dbforums.com>...

>

> > You must be confusing me with someone else. I never said
> anything about

> > how many tables or joins you should have. I was talking about
> having

> > your data integrity enforced centrally rather than in each
> application.

>

> That point didn't escape me. Thank you.

>

> Can you tell me what exactly is the function of the central integrity

> enforcement in the DBMS for a single table with no parent and no

> child? What is required to be done in order to maintain integrity in

> this case?

>

> Regards,

> Mike.

You could have:

1. Uniqueness of key value (always)
2. Domain constraints: e.g.

salary is a number between 1 and 99999 with up to 2 d.p.

status_code must be in list ('A','B','C')

3) Row constraints e.g.

start_date <= end_date

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)

Salary: -47,000,000,000

Date of Birth: 01-JAN-4783

Sex: J

Date Hired: 01-JAN-1066

--
Posted via http://dbforums.com