0

I am having trouble figuring out how to design the relationships between several tables in my project. I'm normally work on the application portion of project, but I've been tasked with DB (PostgreSQL) work. I'm trying my best to understand how to architect the DB properly.

The project is structured very much like a social network (e.g. - Facebook). An individual signs up for the site as a user. A user then has the ability to create and manage a page, such as a business or school. All users and pages have feeds that allow posts. I've put each page type and the users into separate tables because they don't have very much information in common. There are only two different page types at the moment, but more will be added in the future. However, I'm having trouble figuring out how to design the posts table.

Both users and pages can post to any other user's or page's wall. Since both the poster and the postee can be a user or one of the two page types, I can't figure out how to properly store the references to either.

At first, I thought about adding a columns such as from_id, from_type, to_id, and to_type, but it's my understanding that this isn't best practice. Since all the users and pages can cross post, I can't just create a table for each individual post section (e.g. - business_posts).

I've read the following post, but I'm not sure I fully understand the answer or if it even applies to what I'm attempting to do:

Why can you not have a foreign key in a polymorphic association?

Would it be best to create some sort of super table, such as the one shown in the answer above? I'm not quite sure what that would look like. I'm simply looking for someone to point me in the right direction.

Thanks.

Community
  • 1
  • 1
David
  • 1
  • 2

1 Answers1

0

A post has an author (user), and a target (user, business, school). All you need is to store the target_id in the posts table. The post doesn't care about the type of target, just its id.

zyamys
  • 1,609
  • 1
  • 21
  • 23
  • How would it be able to differentiate simply off the `id`? Both the author and the target can be a user, school, or business. Without the type, I wouldn't know what table to point that `id` towards. – David May 12 '16 at 06:12
  • You should make your ids unique, not duplicated across tables. Then you can look for the id in both the `users` and `businesses` table - only one will contain it. – zyamys May 12 '16 at 06:36