IS THIS GOOD DESIGN?

I have a 3 tables
tblAgent, tblClient, tblVendor.
Since each entity requires different info to be stored, it makes sense to
have 3 different tables.
Now each agent, client or vendor can write many notes, so I created the
table like this to store all info
ContactID, ContactTypeID, NoteEnterDate, Note
where
ContactID will contain the id of either agent, client, or vendor (since id
will be the same data type for all 3 tables)
ContactTypeID 1 = Agent
ContactTypeID 2 = Client
ContactTypeID 3 = Vendor
So based on the ContactTypeID passed in to stored procedure as parameter, I
would know which table to join and contactID will be selective to filter
data. Of course there would be no referential integrity with ContactID
since it contains ID from 3 different tables. I am planning to have a
clustered index on ContactID, ContactTypeID (table will have no primary key)
Is this good design? Should I have separate note table for each Agent,
Client, and Vendor?
Can you think of any better design with regard to indexes?
Can you think of any better design to accomodate aforementioned scenario?
ThanksLooks good enough except that
contacttypeid,contactid,notedate (in that order can) be the primary key
clustered, if your notedate is not smalldatetime, of course.
--
"Justin" wrote:

> I have a 3 tables
> tblAgent, tblClient, tblVendor.
> Since each entity requires different info to be stored, it makes sense to
> have 3 different tables.
> Now each agent, client or vendor can write many notes, so I created the
> table like this to store all info
> ContactID, ContactTypeID, NoteEnterDate, Note
> where
> ContactID will contain the id of either agent, client, or vendor (since id
> will be the same data type for all 3 tables)
> ContactTypeID 1 = Agent
> ContactTypeID 2 = Client
> ContactTypeID 3 = Vendor
> So based on the ContactTypeID passed in to stored procedure as parameter,
I
> would know which table to join and contactID will be selective to filter
> data. Of course there would be no referential integrity with ContactID
> since it contains ID from 3 different tables. I am planning to have a
> clustered index on ContactID, ContactTypeID (table will have no primary ke
y)
> Is this good design? Should I have separate note table for each Agent,
> Client, and Vendor?
> Can you think of any better design with regard to indexes?
> Can you think of any better design to accomodate aforementioned scenario?
> Thanks
>
>|||If each row in this notes table will specifically apply to only one of the
other three tables, I would say create 3 seperate tables for notes. This
lets you define the referential integrity constraints and you will know that
all client notes are in one place, vendors in another, etc. Other than the
table structures being similar, is there any other reason why you would want
these all in one table?
"Justin" <jus820@.hotmail.com> wrote in message
news:O3WqCxebGHA.1208@.TK2MSFTNGP04.phx.gbl...
> I have a 3 tables
> tblAgent, tblClient, tblVendor.
> Since each entity requires different info to be stored, it makes sense to
> have 3 different tables.
> Now each agent, client or vendor can write many notes, so I created the
> table like this to store all info
> ContactID, ContactTypeID, NoteEnterDate, Note
> where
> ContactID will contain the id of either agent, client, or vendor (since id
> will be the same data type for all 3 tables)
> ContactTypeID 1 = Agent
> ContactTypeID 2 = Client
> ContactTypeID 3 = Vendor
> So based on the ContactTypeID passed in to stored procedure as parameter,
I
> would know which table to join and contactID will be selective to filter
> data. Of course there would be no referential integrity with ContactID
> since it contains ID from 3 different tables. I am planning to have a
> clustered index on ContactID, ContactTypeID (table will have no primary
key)
> Is this good design? Should I have separate note table for each Agent,
> Client, and Vendor?
> Can you think of any better design with regard to indexes?
> Can you think of any better design to accomodate aforementioned scenario?
> Thanks
>sql