CODEWITHSUNDEEP
mysqldatabase-designdatabase
Terminal
Terminal

Reputation: 1999

Unable to figure out correct database design

I am designing database for social networking site with "followers" and "following" feature like twitter. There are some relations which are common to whole circle i.e to whom i am following and to my followers too.. I have a table Following with uid1 and uid2. Suppose A follows B and B follows A and C. It will have entries like

uid1 uid2
A      B
B      A
B      C

Now i want a third field relationId in the same table which should be unique to a relation whether A follows B or B follows A like

uid1 uid2  relationId
A     B      1
B     A      1
B     C      2

so that i can use relationId as foreign key in another table to store features common to whole circle of a user. One thing immediately comes to mind is if there can be a commutative formula to fetch a unique number( relationId) given two numbers(uid1 and uid2). But what it can be?

Edit

I also have one more doubt. Since username in my database is unique. So shall i use username as primary key in whole database or do we have a performance benefit by using a number as primary key as uid in my case with a table to resolve uid and username?

Upvotes: 0

Views: 79

Answers (1)

Matthew Flaschen
Matthew Flaschen

Reputation: 285077

I would do it similar to the way you have it:

Follows
-------
follower followed
A        B
B        A
B        C

This could obviously have information like followDate which makes sense in both directions.

Then you can also have:

Connected
-----------------
relationId uid1 uid2
x          2    3

with relationId an artificial key (could be INT AUTOINCREMENT, GUID, etc.)

However, Connected would have a constraint (whether you can enforce this in the db layer depends on your system) that uid1 < uid2. Obviously, this also implies you'll never have it both ways.

This is relatively simple, easy to query, and should have decent performance with a good composite unique index on uid1 and uid2. When searching for non-directional information about the relationship, you don't have to look at indices for both directions.

EDIT: I would recommend using an artificial key for users. Even if you don't allow username changes now, you may want to do so later.

EDIT 2: According to this, you can reject an insert in a trigger by setting a NOT NULL column to NULL. Both uids should be NOT NULL, so you can just set one to NULL if uid1 >= uid2.

Upvotes: 3

Related Questions