Table design advice

F

fizzled

Well-known member
Joined
Aug 9, 2004
Messages
52
Location
Atlanta, GA, USA
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 Ive 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 Ive come up with, Im 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 rows 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 Im checking is a boolean field when Im retrieving Active or Archived articles, instead of having to compare the dates on every row.

Does anyone have any thoughts, suggestions, or opinions?
 
In the example you gave where an article is archived before it expires, I would not change the expiration date. Thats corrupting the data, IMO. If the article can be archived before it expires then use both fields.
 
Our current DBA and a guy from Microsoft SQL Server team recommends NEVER allowing NULLs on columns that would be indexed/searched. Indexes cant account for NULLs very well. From that advice, weve gone to using both end dates and active fields and its quite nice.

The experts offer an alternative which is to always set the expiration date. For rows where its 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 dont know about the performance of a true/false field. Internally Id hope its a bool value of some kind.

All the above having been said, were talking about Access. I really wouldnt worry about performance hardly at all there. If youre considering performance at the detail level, then maybe Access isnt the right DB.

-ner
 
Back
Top