0

i want to design a users table with the following fields in hypertable database:

rowkey       : (unique Guid)
username     : (unique in the table)
email        : (unique in the table)
passwordHash : (string field)
passwordSalt : (string field)
firstName    : (string field)
lastName     : (string field)

now the rowkey is the id of the user and i refer to this id a lot of stuff that belong to user (i understand that in hypertable the only auto primary key options is guid) the reason i won't use the username as the rowkey is because the username can be changed if changed occurs i will have to update a lot of stuff in the entire database. same role append also to the email field.

now user can login with his username or with his email. so i will need to retrieve entire row by email or by username.

can you help me design this table thanks.

Laurent Parenteau
  • 2,516
  • 20
  • 31
ygaradon
  • 2,198
  • 2
  • 21
  • 27
  • Why don't you use e-mail as the rowkey? That would be ideal for this - especially if you have to look up by e-mail. – Suman Jun 06 '12 at 22:40
  • if the user will change it email then i will need to update all the references in all the table. i need in the key some value that will not change. i will have the same amount of quires for the id, and email and username – ygaradon Jun 07 '12 at 00:16
  • Ah I see, will only the e-mail change or the username as well? If either or both of these can change, you will need to use a index - either in HBase or external. – Suman Jun 07 '12 at 14:50
  • the email and the username can change. in hypertable they have the build-in indexes. i don't know if hbase also have build-in indexes – ygaradon Jun 07 '12 at 15:06

1 Answers1

1

If you download hypertable sources (or browse the sources online) then you will find a PHP example project which implements a twitter-like microblogging site. And basically it implements the same user table as you asked for (with one difference: it uses the username as the row key). But you can easily change this and use the GUID instead.

(Although, from a user's perspective, i think you very rarely change the username, and i do not know any webpage that offers such a function. Changing email address or location is of course necessary.)

Here is the script which generates the database (look for the "profile" table): https://github.com/cruppstahl/hypertable/blob/v0.9.5/examples/php/microblog/setup.hql

cruppstahl
  • 2,447
  • 1
  • 19
  • 25
  • my problem with the twitter example is that the user login with it email or with it username so i need to query by email or username. and not by the guid(rowkey). hypertable is column oriented database so for query on two columns i think the best way is to use the column qualifier feature for the username and email columns fields for example: login:username login:email and then make a query like "select login from accounts where login = "username/email" and then i will find the rowkey and query again by rowkey. – ygaradon Jun 12 '12 at 23:00
  • If you use username (or email) as a row key then your only problem is that you have to copy all those rows as soon as the user changes its username (or email). Other than that there's no disadvantage. But if you want to use the GUID then you can simply have one column family which maps usernames (or emails) to GUIDs, i.e. the cell value contains the username, and the row key is the GUID. Then run 'select guid from accounts where guid = "email@blah.com"' and fetch the row key. Make sure to create a secondary index on the "guid" column family. You would not even need column qualifiers for that. – cruppstahl Jun 13 '12 at 06:14