Table design question - subject matter expert database

Getting started with SQL Server

cbolwerk


I would like to create a database that keeps track of our companies subject matter experts. I have roughed out some of the tables. I would appreciate any feedback on if this is the right approach and if there might be any issues when I start writting a front-end (probably VB 2005).

What makes this interesting (at least for me) is that a subject has an owner and at least 1 "expert", possibly up to 3. Here is what I am thinking for tables:

tblEmployee

EmployeeID (PK)

LastName

FirstName

etc......

tblSubject

SubjectID (PK)

Description

tblOwner

SubjectID (FK)

EmployeeID (FK)

tblExpert1

SubjectID (FK)

EmployeeID (FK)

tblExpert2

SubjectID (FK)

EmployeeID (FK)

tblExpert3

SubjectID (FK)

EmployeeID (FK)

Does this make sense Would I run into any issues when trying to display this on a form in VB

Thanks in advance for any help!!!!!

Cal

Top

Re: Table design question - subject matter expert database

Jens K. Suessmeyer


I wouldn¡¯t scale the expert out to n-tables:

tblExpert

Subjectid (FK)
EmployeeId (FK)
(Both columns a compound PK)

If you want a precedence in the experts you can introduce another column e.g. priority / Order.

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---






Top

Re: Table design question - subject matter expert database

Arnie Rowland

It doesn't make sense to have three separate tables for your SMEs.

Just create ONE SME table.

In fact, you may wish to consider rolling the SME and SO tables together, adding a flag column to indicate 'Owner'. (An 'owner' may also be an SME.)

Reduces and simplifies the JOINs required.

(Or add an 'Owner' field to the Subject table.)






Top

Re: Table design question - subject matter expert database

ggciubuc

I see this complicated.

The future issues can be an querying level because you have to join many tables.

So I should do in this way:

tblEmployee

EmployeeID (PK)

LastName

FirstName

etc......

tblSubject

SubjectID (PK)

Description

tblExperts

SubjectID (FK)

EmployeeID (FK)

Level --1 mean first expert, 2- second and 3 third

Owner - Yes or No if this expert is or no owner of the subject

In this way you can have a more readable and flexible database schema





Top

Re: Table design question - subject matter expert database

cbolwerk

Thanks to everyone for the help!!!! All of these answers are very helpful. I think that I'll combine the owner and experts into one table. I'm thinking that it would be best to keep things separated as much as possible.

Can I mark all of these responses as "answers" I'm going to give it a try!!!

Thanks again to all of you! This has been a wonderful and educational experience!

Cal


Top