Problem on relation of tables in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi I'm developing a sql database, but I have faced with a problem in relation of tables. Assume that we have a table named "TASK" that contains these fields: id, TaskName, Type, startDate, endDate. Each Task has different attributes and details. So I have to create a table for each Type of task and connect Task to these Tables.

My question is how can I connect "Task" to other tables?

If you know a better way.

Please help me.

Thanks for your answers.

Posted 1-Feb-11 23:51pm

mojtaba pirveisi

Updated 2-Feb-11 0:05am

Henry Minute

v3

Add a Solution

Comments

shakil0304003 2-Feb-11 6:00am

Not clear!!!

Chris Maunder 2-Feb-11 6:30am

"Each Task has different attributes and details": What do you mean? Are there more fields depending on the type of Task?

3 solutions

Solution 1

You can create one table for attributes and store all the attributes in the single table for a given task.

Posted 2-Feb-11 0:03am

Om Prakash Pant

Updated 2-Feb-11 0:28am

Chris Maunder773.1K

v2

Solution 2

you need to create a table where you store tasks and a table where you store different types of tasks. In the taks table (1st) you set the ID of row of the specific tasktype (2nd table)

For a tutorial about relationships:

http://www.functionx.com/sql/Lesson11.htm[^]

Posted 2-Feb-11 0:12am

AschB

Comments

Henry Minute 2-Feb-11 6:18am

That would be an excellent link were it not for the fact that it deals with MySql and therefore gives incorrect syntax for SqlServer. This will confuse the OP who is obviously a beginner.

AschB 2-Feb-11 6:44am

Whoops my bad. Sorry

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Henry Minute · Accepted Answer · 2011-02-02T00:15:00

Solution 3

You need to implement a foreign key constraint in your Task table.

Take a look at this page[^] from MSDN and also follow the links on the page. In the diagram The 'SalesOrderHeader' would be your Task table and the 'SalesPerson' would be the new table you should create for TaskType.

TaskType should contain at least TaskTypeID and TaskTypeDescription columns.
TaskTypeID should be the Primary Key NOT NULL. It should be an Integer datatype column and you can make it an Identity column if you wish.
Change the Type column in your Task table to be an Integer datatype.

Then follow the pattern shown in the link.

Posted 2-Feb-11 0:15am

Henry Minute

Comments

mojtaba pirveisi 2-Feb-11 8:54am

Thnk for your answers but I know how to create relationship between tables , My problem is that each Task has different attributes , for example , task number 1 is "typing" , I want to know how many pages has been typed so far , which font has been used and etc . Task number 2 is "Image Editing" , I want to what the progress is ( for example 35%) , whether image has been cropped (yes or no ) and etc . Ss you can see I have to create different table because of different column for each task . If I have a table for "Task" , one for "Typing" and another for "Image Editing" , How can I connect these two tables to "Task" ?
Thanks again .

Henry Minute 2-Feb-11 9:24am

The first thing that occurs to me is that if you are able to enumerate all of the possible categories of task you might adopt a similar structure to that used in an Accounting program where there is a main category (e.g. Stationery) with sub categories (Pens, Notepads, Paperclips etc...) each category having an ID. Even with that you still have the problem of many tables because of the different attributes.

Another approach might be to follow the design of a typical 'Time Sheet' application where again there is the main Task with ID and designated Sub-Tasks with IDs but the rest is more free-form.

If you think that you could model your requirements using an Object Oriented language (A Base Class with derived classes adding to the properties of that 'parent' for each sub-task) then the article at http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server might be of use.

Sorry to not be more help. If I think of anything else I'll come back to you.

mojtaba pirveisi 2-Feb-11 11:10am

Thanks . That helps me a lot .