User based system

Question

This is my first web application based on SOA and is user-specific. I have three layers;

1. web
2. service
3. database

for eaxample, I have a table graph like this:

("<---" = foreign-key)
User<---Post<---PostTitle

Now my service need to allow web to load PostTitles directly so the user can see it. However, this action must by user-specific, which means Mike cannot load Sam's data.

Problem is: My [PostTitle] table only foreign key [Post] table, it doesn't have any direct information about which user each record belongs to, so I have to do table join. It's fine for this simple case, but if I have a long table chain, it will be very trival to join from the end to the begining.

I think the most obvious solution is to add a UserID column on each table, so that I don't need to do joining. However, I feel it is a kind of wasting database space.

I hope someone who have experienced this kind of system can give me a real world suggestion on my design. The suggestion can be on any layer, besides database.

Answer 1

You need to decide what's worse:

A) Nasty, long, expensive, muti-joined queried to the DB

OR

B) A few more bytes in some tables

Personally I use a framewort that handles option A for me, so I'm happy with it.

Also, why is PostTitle seperated from Post?

Answer 2

A graph database is a good choice when you want to connect many different kinds of entities. Graph databases give you easy access to the "chain" of entities. By using typed relationships you can easily traverse into the relationships of interest from any starting point. Here's some screenshots of domains modeled using a graphdb, to get you started thinking in graphs!