I have the following db schemas:
Comments table
and Posts table ( has FK to Comments table) :
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
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.
