0

Let's say I have a Post table, where an entity can post comments. I could do a simple 1 to many between the entity & post tables. However, I can also have users who are not part of an entity post...as well as anonymous users post. To boot, users and entity have PK with different data types.

I could create a Post table and use two separate fields to hold the different FKs, but what happens tomorrow when I need to add another thing that can post? And wouldn't this affect how the Entity Framework works with the database?

Charles Demar
  • 41
  • 4
  • 1
    post some sample code or database layouts – I wrestled a bear once. Feb 19 '16 at 21:15
  • 1
    What DBMS are you using? Capabilities differ. – philipxy Feb 20 '16 at 08:02
  • 1
    The phrase "will be related" is ambiguous, although it may not seem so at first. If the relationships are inherent in the subject matter, then they are related, whether you know it or not. If you mean that you will use the relationship for joining purposes, then it depends on how you are going to use the data. – Walter Mitty Feb 20 '16 at 11:33

2 Answers2

0

Ok, you have a Post table.

Post
----
Post ID
Post Text
Post Time Stamp
User ID
...

Where Post ID is an auto-incrementing long and the primary key of the Post table. The User ID is a foreign key back to the User table, or null if an anonymous post.

User
----
User ID
User Name
User Password
...

However, I can also have users who are not part of an entity post.

You're going to have to describe this in way, way, way more detail.

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
0

Foreign keys ("relationships") are not needed for querying. They are for integrity. They tell the DBMS that a list of values for a list of columns of a table have to appear as a list of values for a different list of columns forming a candidate key in a table. An SQL foreign key just has to reference columns that form a UNIQUE column set ("superkey"). PRIMARY KEYs are just a special case of that.

If you would like to tell the DBMS that the author of a post has to be from among several disjoint tables, there's no declarative way in most SQLs. However you can organize tables and columns differently so that you can enforce declaratively:

Create a table Poster with a poster_id PK and poster_type column with UNIQUE (poster_id, poster_type). Then give the various poster tables those columns too as a FOREIGN KEY referencing Poster. Each subtype of poster (entity, user, anonymous) has its own value for poster_type, which is the same throughout its table. Poster CHECKs that poster_type is one of the valid subtype values; each subtype table CHECKs that its poster_type is its own value.

For some other approaches see this answer re "Foreign Key to multiple tables". Or this answer. Google subtyping in SQL.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83