Add new feature to existing classes (C#)

In a c#.net application I have a two classes as below

Employee

EmployeeId : int : Primary key

Name : string

Job

JobId : int : Primary key

Name : string

EmployeeId : int

In database (SQL Server) I have two tables to save data from those classes (Employee & Job) (ID fields are auto increment fields so can have same no in both tables)

Now I need to implement a new feature to give users to add comments for Employees or for Jobs, So I need to create a single Comment class to share by both classes.

What is the best Class & Tabl开发者_Go百科e Structure to implement this new feature.

You could create the following tables...

Comment (CommentId, Text, [other fields...])

JobComments (CommentId, JobId)

EmployeeComments (CommentId, EmployeeId)

.. and then you could use either table per type inheritance (if using EF see here) or you can do it with standard relationships.

I'd recommend a database design like which allows adding more comment types in the future without the need to modify your database.

Table Comment

Id (int, Primary Key)
CommenteeId (int, not null)
CommentTypeId (int, not null)
Text (nvarchar(255))

Table CommentType

Id (int, Primary Key)
Name (nvarchar(50), not null)

CommenteeId will reference Job or Employee or any other table you'd need to comment in the future.

CommentTypeId will reference CommentType where you can add types as needed.

As for your class design it could look like this:

public class Job
{
    public List<Comment> Comments;
    public string Name { get; set; }
}


public class Employee
{
    public List<Comment> Comments;
    public string Name { get; set; }
}

... as a basic idea. How and when your Comment is loaded as part of Employee or Job is depending on your method of accessing your database.

If there is no differnece between Comments for Jobs and Employees i would suggesst next:

1. Add Table "Comment". I am not sure about your business logic, but it would have next filds i suppose: CommentdId, UserId, IsJobComment ( this will be bit value which will show difference between cmmments for job or employee), IsEmployeeComment ( will show if this is comment for Employee ), Text, RelatedItemID( this will contain id of the related job or employee )

2. Class structure: Add List to classes Job and Employee

3. Add object comment with values: ID, UserID, Text.

Update: One more idea.

Instead of adding IsEmployeeComment and IsJobComment change this to simple column CommentTypeID.

Then add Table CommentsType which will have next fields: 1.CommentTypeID 2.CommentType - string value which will define type: Employee or Job. So, when new comment type added: just add value to this field.

Database-wise I would prefer not to need a separate table for each type of comment.

For instance the Employees and Jobs tables could have a CommentID column, and the Comments table would have something like this:

CommentID int
RelatedCommentID int
CommentText ntext

Then you would set RelatedCommentID=CommentID for the first and all following comments.

For this it is better to create two tables: JobComments and EmployeComments.

Content for both is the same:

CommentId : int : Primary key & auto_increment

ReferenceId : int : Foreign key

Content : varchar

Maybe you need some more fields, based on your needs.

I personally would just add a comment column to each table, and string variable to each class.

In your database if you wanted to have the comments in a seperate table I think you would need two seperate tables which seems a bit pointless for the data which is being stored. You would need the two tables because the IDs of each could conflict if you keep the comments for both Job and Employee in one table.

In the software what do you do to comments other than get / set the value that would warrant having this in a seperate class? It seems like using an extra class for the comments would just overcomplicate things.

If you wish to keep in one table:

Comment:
JobCommented : int <- references a job
EmployeeCommented : int <- references an employee
Comment : string

Both JobCommented and EmployeeCommented are nullable. Whichever is filled connects to the parent.

I would create a comment table

commentId NUMBER;
commentText VARCHAR2;

and two relation tables:

EmployeeComments

commentId NUMBER;
employeeId NUMBER;

and JobsComments

commentId NUMBER;
jobId NUMBER;

then select comments with inner joins.

This should respect at least first three forms of db normalization.

PS: of course there'd be some Foreign Keys just to be sure to don't forget orphaned comments.

For the objects in c#, well, just read with a left join the tables.

I'd probably create a single class Comments and have a List of them in each of your existing classes. If there are any variations then you can always subclass into JobComments and EmployeeComments.

For the database I would probably actually keep the two tables separate. This is in part because I think that it will probably be easier to do so in terms of worrying about identities. If they are in the same table as each other then you'd need to worry about a compund key of ParentTable and ParentId or similar.

Also if the two comment types ever diverge then it will be much easier to have them in separate tables in the database.