Posted by Ed Murphy on 02/28/07 00:20

--CELKO-- wrote:

> Let me be the bad guy -- I have practice -- and elaborate on Ed's
> remark.
>
>>> MyTable {field1, field2, startdate, enddate} <<
>
> is useless. Ignoring the bad column and table names, ignoring the
> confusion of columns with fields implied by your bad choices,

We still need a URL to answer the perennial "what's the difference
between columns and fields?" question.

> this could be any of the following:
>
> CREATE TABLE MyTable
> (field1 ?? DEFAULT ?? [NOT NULL]
> [CHECK (??)],
> field2 ?? DEFAULT ?? [NOT NULL]
> [CHECK (??)],
> start_date DATETIME [NOT NULL],
> end_date DATETIME [NOT NULL],
> [CHECK (start_date [< | <=] end_date)
> [PRIMARY KEY (??)] );

Most of it is likely irrelevant in this particular case, though of
course it takes some experience to make such a judgment correctly.

* field1's datatype and nullability probably don't matter, as all
we do with it is group by it.

* field2 can be 1, so it's some numeric type; its nullability
probably doesn't matter, as we seek a specific non-null value.

* start_date and end_date's nullability might matter. If start_date
is within the desired range and end_date is null, should that row
be included? Etc.

[Back to original message]