asked on

Table Design Question

Hi,
I am using SQL Server 2005 and I have a "simple?" table design question.

I have an "Article" table that has basic information about an article on the web

ArticleID
Status
PostDate
Accepted (true/false)
URL
Comments
etc

Now if an article is rejected by an adminstrator, they have to indicate 1 to 5 reasons it was rejected (via checkboxes on the page)

- reason 1
- reason 2
- reason 3
- reason 4
- reason 5

- Other (50 characters max)

So do I just add these as linear columns to the Article table (5 - bit columns and a varchar(50)? Or do I created an ArticleReject table (what would that look like?)?

Thanks,
hefterr

lcohan

I would add the lookup table ArticleRejectReason as you noted above and store in the parent table column the Id for the reject reason from loookup table.

something like:

CREATE TABLE ArticleRejectReason
(reasonId int,
Reason varchar (255))

And store pairs like:

1,reason 1
2,reason 2
3,reason 3
4,reason 4
5,reason 5

Then only 1,2,3,...will be stored in the parent table as RejectReasonId

hefterr

ASKER

Hi lcohan
The main Article Table can have 5 reasons for the rejection or a text description (other).

I'm not sure how this fits into your design. Where would the 5 reasons and a possible text reason?

lcohan

Like this:

CREATE TABLE Article
(
ArticleID
Status
PostDate
Accepted (true/false)
URL
Comments
RejectResonId -- here's where you store the Id of the row matching the reason from the lookup table.
etc
)

This way you can add new reject reasons to the lookup and use them in parent at any time however.....if you are 100% certain that never ever there will be more than those 5 then....you could just store them as they are in the Article table in a RejectReson varchar column but make sure whatever populates the table can put only ONE and alwys the same text for that same reason otherwise will be a mess.

hefterr

ASKER

But a single article can be rejected for 5 different reasons (AND and additional "other"). It seems to me that you are only allowing for 1 reject reason. This is the core of my question.

Is it OK to define 5 RejectReasonIDs (RejectReason1, RejectReason2 etc). And a RejectOther (varchar 50).

lcohan

I see...well in that case you can concatenate all selected checkboxes reasons description into one string and store that one in the Article table on the RejectReason column.

hefterr

ASKER

It's an idea. But I'm not wild about compressing data intoa column. If I have to seach the Article table for all articles rejected due to reason "inappropriate", I have to parse each column or do some sort of LIKE.

Do you think it is bad to create 5 columns for reject codes where null is allowed?

ASKER CERTIFIED SOLUTION

Chris Luttrell

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

hefterr

ASKER

Thanks. I get your approach. It's just the "comments" is really a reject reason defined by the user.

Thanks.

hefterr

ASKER

If you don't mind 1 last "late hit" question.

What would the SQL be to list all the articles and the reject reason (readable). If the reason was entered (for a particular article) by the user, then that is returned also.

Example:

ArticleID Reject Reason
1 reason 2
1 reason 3
2 inappropriate
3 reason 1
3 bad language

Thank,
hefterr

Chris Luttrell

Based off the tables I gave you, with the Comments in the Xref table, this query would give you results like you described.

SELECT A.ArticleId, CASE RR.RejectReasonID WHEN 0 THEN ARRX.Comments ELSE RR.Reason END AS 'Reject Reason'
FROM dbo.Article A
INNER JOIN dbo.ArticleRejectReasonXref ARRX ON A.ArticleID = ARRX.ArticleID
INNER JOIN dbo.RejectReason RR ON ARRX.RejectReasonID = RR.RejectReasonID

Open in new window

hefterr

ASKER

@ CGLuttrell,
Thank again. Nice design!