Table design advice

[fizzled]

I currently have a table (in Access) to store information for articles such as Author, Headline, ArticleText, PostDateTime, etc. In addition to the regular information, these articles can also be considered Active or Archived, so I've tossed in a true/false IsActive field. Finally, I plan to let the author set an expiration date, ExpDate, for the articles, whereby the article will automatically be considered Archived if it is viewed after the expiration date.

 

tblArticles:

ArticleID (AutoNumber)

Author (Text)

Headline (Text)

ArticleText (Memo)

PostDateTime (Date/Time)

IsActive (Yes/No, formatted to True/False)

ExpDate (Date/Time)

 

So my question is this: After looking at the table I've come up with, I'm suddenly wondering if the IsActive field is just a waste of space, albeit a small one. Anytime an author/administrator chose to archive an article, I could simply update the row's ExpDate with the current date (or the date of the previous day, depending how I coded it) and forgo the IsActive check. However, I was also thinking the SQL execution would probably be faster if all I'm checking is a boolean field when I'm retrieving Active or Archived articles, instead of having to compare the dates on every row.

 

Does anyone have any thoughts, suggestions, or opinions?

[Machaira]

In the example you gave where an article is archived before it expires, I would not change the expiration date. That's corrupting the data, IMO. If the article can be archived before it expires then use both fields.

[Nerseus]

Our current DBA and a guy from Microsoft SQL Server team recommends NEVER allowing NULLs on columns that would be indexed/searched. Indexes can't account for NULLs very well. From that advice, we've gone to using both end dates and active fields and it's quite nice.

 

The experts offer an alternative which is to always set the expiration date. For rows where it's not expired set it to a "max" date so that queries always return rows. Personally, I like the true/false field as it makes the code easier to read.

 

Now this knowledge is for SQL Server where bit fields are much faster than dates, especially dates with nulls. In Access, I don't know about the performance of a true/false field. Internally I'd hope it's a bool value of some kind.

 

All the above having been said, we're talking about Access. I really wouldn't worry about performance hardly at all there. If you're considering performance at the detail level, then maybe Access isn't the right DB.

 

-ner

[fizzled]

Performance isn't really that big an issue right now, I was just wondering for future reference mainly. Thanks for the advice.