USER1(int)(PK) | USER2(int)(PK) | STATUS(varchar) | FRIENDSHIP_DATE(date)
USER1 and USER2 are the two users having a relationship between them. USER1 > USER2 (always), so that only a single row will be maintained in between two users. The relationship between them will be justified by STATUS column. STATUS can hold below values only:
The question I have is:
Is it a good approach to hold varchar values in STATUS columns, or I can use 1,2,3,4,5,6,7,8,9. Because having int values will require less space.
Or should I create a separate table as: RELATIONSHIP : ID(PK)(int) | LABLE(lable) and map One-to-One with USER_FRIENDS table ?
Knute Snortum wrote:That's what I would do.
And the reason for this would be?
Because keeping a column(having int value) give good performance vs keeping an FK.
Would you take this decision because 1,2,3,4,5,6,7,8,9 would not take extra space for each row if referred by the FK key? This will take less space, right?
Yes, there is a bit of a performance hit when you JOIN the two tables. This is the old debate about over-normalizing a database, but in this case I this the advantages outweigh the disadvantages.
For relatively small sets of code values, a code foreign key to a lookup table wouldn't be all that inefficient if the system cached the lookup table in RAM. This is one of the things you get pretty much for free when using an ORM.
Thanks Knute. A senior at my work recommended me to avoid over-normalizing and before implementation, I always prefer to ask some experts over forum.
Knute Snortum wrote:Yes, there is a bit of a performance hit when you JOIN the two tables. This is the old debate about over-normalizing a database, but in this case I this the advantages outweigh the disadvantages.
Tim wrote: For relatively small sets of code values, a code foreign key to a lookup table wouldn't be all that inefficient if the system cached the lookup table in RAM. This is one of the things you get pretty much for free when using an ORM
Thanks Tim, for recommending the Enum.
I still have one doubt regarding the mapping of User_friends and friendship_status (new table). Should I use One-to-One or Many-to_one ?
The rule I apply for taking a decision over this is: user_friends can have only one status (so the One towards friendship_status) and one status(suppose FRIENDS) can belong to multiple rows in user_friends(So many towards user_frientship). So User_friends - friendship_status is Many-to-One. Is it correct?
There is, by the way, an entire class of database where relationships are as important (or more so) than the data itself. The Neo4J database is an example of this.
Unlike JDBC, Neo4J is not a SQL DBMS and it has its own rather idiosyncratic query language, since SQL isn't well-suited for the kinds of queries you'd do. It is well-documented, though, and there is a Spring Data sub-project devoted to it.