0

I have the following db schemas:

Comments table enter image description here

and Posts table ( has FK to Comments table) : enter image description here

As you can see, there is a set of the same columns for both tables. Is it bad design? If yes, how can I avoid it? Also I want to use Entity Framework to map this entities. I tried to use inheritance and table-per-hierarchy approach , but unsucessfully (difficult to understand how to map self-referencing table)

Regards

  • I'm not sure I would even use more than one table here, but that's strictly from the database design perspective. Since you want to use EF, that may change for you how things should be implemented. – Andriy M Oct 11 '16 at 16:39
  • Your question may have an answer here: stackoverflow.com/questions/13749525/… – Walter Mitty Oct 12 '16 at 4:45
1

Having identical column names across tables is a good thing and a common design practice. It makes writing queries easier because the naming standards are more consistent. When joining them in a query, you'll just have to qualify each identical column name with the table name or alias.

If you wanted to get all the post titles and any comments along with the date the comment was made, it would look like this:

SELECT p.Id, p.Title, c.CreatedOn, c.Content
FROM Posts AS p
LEFT JOIN Comments AS c ON c.PostId = p.Id

If you leave off the table name or alias from the CreatedOn column (the "c.", in this case), SQL Server can't tell which CreatedOn column you're asking it for, and you'll get an error like this:

Msg 209, Level 16, State 1, Line 1

Ambiguous column name 'CreatedOn'.

Technically, you can get away with not aliasing the unambiguous columns (like Title, in this example, because it doesn't exist in the Comments table), but it's better to alias all or none of the columns for readability's sake.

Is what you're doing with two tables a bad design? If my assumption is correct that there can be multiple comments per post, you're right to split them into separate tables. It's a true one-to-many relationship and those belong in separate tables. Entity Framework shouldn't have any trouble with this design.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.