Solved

Help with table design?

Posted on 2009-07-10
2
202 Views
Last Modified: 2012-05-07
hi,

Can you take a look at my tables and see if I have correct PK & FK between tables.

MembersTable(userid,lname,fname,phone,office,createddate,createdby,modifieddate,modifiedby)
ProjectsTable(pid,pname,pstatus,startdate,enddate,customer,assigner,assignee,createddate,createdby,modifieddate,modifiedby)
TasksTable(tid,pid,taskname,parenttaskid,tdetail,tstatus,ttype,startdate,enddate,customer,assigner,assignee,createddate,createdby,modifieddate,modifiedby)
TaskTypesTable(ttid,taskType)
task might have a project. project will have at least one task.
userid will be used in assigner, assignee,createdby,modifiedby
Do I make assigner, assignee, createdby,and modifiedby as FK in ProjectsTable & TasksTable?

Thanks.
0
Comment
Question by:dkim18
2 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 300 total points
ID: 24828582
well, it is good to have FK for UserID so that even by mistake nothing could be entered in child table which is not exists in parent table.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 200 total points
ID: 24830361
You'll want foreign keys for:

Members.UserID to Projects and Tasks (can't tell what fields...they should be named the same)
Tasks

Pretty much any type of lookup table you'll want to create a foreign key for integrity purposes.  You might also want to cosinder being a little more descriptive on your field names....for example, you could potentially create a FK from your members table to your modifiedby, createdby, customer, assigner fields, etc.  You know what I mean?
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now