Win a copy of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

varchar vs int for column in Table  RSS feed

 
Ranch Hand
Posts: 611
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a USER_FRIENDS table having the following structure:
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:
FRIENDS
PENDING_FIRST_SECOND
PENDING_SECOND_FIRST
BLOCK_FIRST_SECOND
BLOCK_SECOND_FIRST
BLOCK_BOTH
FOLLOWING_FIRST_SECOND
FOLLOWING_SECOND_FIRST
FOLLOWING_BOTH


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 ?
 
Sheriff
Posts: 5121
138
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Puspender Tanwar wrote:
Or should I create a separate table as: RELATIONSHIP : ID(PK)(int) | LABLE(lable) and map One-to-One with USER_FRIENDS table ?


That's what I would do.
 
Puspender Tanwar
Ranch Hand
Posts: 611
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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?
 
Knute Snortum
Sheriff
Posts: 5121
138
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It has to do with good database design.  1-9 are meaningless until a program interprets them, so the meaning is hidden in the program code.  Changing the meaning would mean a change in the program everywhere the code is dealt with.  With a foreign key, you make one change to the database and it's done.  You can change a label, add a label, all without programming.  Also your SQL queries would contain meaningless numbers that would have to be interpreted.

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.
 
Bartender
Posts: 19730
92
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is classic code-and-lookup operation. Historically it's been a more efficient use of disk space to do it that way. These days I'd not be so sure, since modern databases could certainly track values and compress them (possibly into internal codes) without getting significantly more complex or intelligent than they are. And, in fact, might even use space more efficiently that way. But as a general rule, I still prefer the old explicit lookup. Which you can map to a Java enum for programming convenience if the set of codes is fixed.

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.
 
Puspender Tanwar
Ranch Hand
Posts: 611
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.

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.

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?

 
Tim Holloway
Bartender
Posts: 19730
92
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, if the relationship type was simple, then you could have a ManyToMany linkage between people. But even then it would be easier to maintain a link table (at least when you're working with JPA). So for your needs, a link table where the IDs of the two people are foreign key columns and the relationship type is a value column would be best. You can further constrain the relationship by making both person IDs be part of a compound key or unique constraint on the link table.

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.
 
Puspender Tanwar
Ranch Hand
Posts: 611
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
After reading some articles and questions, I decided to use ENUM('UNKNOWN', 'FRIENDS', 'PENDING_FIRST_SECOND', 'PENDING_SECOND_FIRST', 'BLOCK_FIRST_SECOND', 'BLOCK_SECOND_FIRST', 'BLOCK_BOTH') as the datatype for status
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!