one table or split into two?

[Guest]

I want to create a tblSuspension for my clients. There are currently two
types of Suspensions, NoShow or Conduct. When looking at a client's history
or determining the length of a suspension, one type of suspension has nothing
to do with the other.

In a report, I may do a count of the types of suspensions, but having nth
NoShow Suspension won't be counted against the client when looking at a
conduct suspension.

Pretty much all of the fields would be the same for either types. There may
be a few extra fields that one type would use and the other wouldn't. Should
I have one table called tblSuspension with a field SuspensionType to
distinguish the difference... or should I have two tables?

How do you determine if the suspensions should be in one table vs splitted up?

 

[Vincent Johns]

I want to create a tblSuspension for my clients. There are currently two
types of Suspensions, NoShow or Conduct. When looking at a client's history
or determining the length of a suspension, one type of suspension has nothing
to do with the other.

In a report, I may do a count of the types of suspensions, but having nth
NoShow Suspension won't be counted against the client when looking at a
conduct suspension.

Pretty much all of the fields would be the same for either types. There may
be a few extra fields that one type would use and the other wouldn't. Should
I have one table called tblSuspension with a field SuspensionType to
distinguish the difference... or should I have two tables?

How do you determine if the suspensions should be in one table vs splitted up?

There are various good ways to do that, but I usually opt for whatever
takes the least space. My guess here is that you should use just one
table, and leave some fields blank. But you might want to have one
table for the common fields (such as last name) and two others with
unique fields, each linked to the primary key of the first table.

With your tables set up, you can write queries based on them that will
behave a lot like tables, but without duplicating information.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.