Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

querying question

 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

This isn't technically a Java question, but as I am writing an application in Java and will be asking lots of Java related questions here over the coming weeks as I learn things and come across things I do not fully understand, I hope no one minds me asking this question here - saves me from having to sign up to a database forum!

Basically, my application will let people reigster, and their URL will be of the format http://<domain>/Joe.Bloggs.NN

So say for example you sign up , and you enter your name as Joe Bloggs, your personal URL will become:
http://<domain>/Joe.Bloggs.1

Now say I sign up as Joe Bloggs next, mine will become
http://<domain>/Joe.Bloggs.2

And so on...

Now my question is this, say two more Joe Bloggs are signing up at the exact same time, and they both press "Submit" at the same time. Both their sessions go to the database, find out that the last entry for Joe Bloggs is 2, and so both sessions will try and register as Joe.Bloggs.3. Now as this column will be unique, the session that tries to insert last will fail as the other session would have inserted this value.

My question is how would you handle this? My idea is that as this event is expected to be very rare, to simply have an exception block that catches this and tries to insert the next value instead. Would you agree, or is there a "better" way to do this? Good design is very important to me so I would like to learn about best practises here.

Thanks.

A



 
Les Morgan
Rancher
Posts: 710
17
C++ Java MySQL Database Netbeans IDE Oracle Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Let me see if I understand:

You are sending them to the DB to check for the next session value for Joe Bloggs, both get the value 3 back.

I would look at synchronizing the code that does that step.
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Les Morgan wrote:Let me see if I understand:

You are sending them to the DB to check for the next session value for Joe Bloggs, both get the value 3 back.

I would look at synchronizing the code that does that step.


Well, each user will be registering through their own HTTP Session, and when the user clicks submit, the code checks if the FirstName.LastName exists in the database, if it doesn't, it creates a URL with FirstName.LastName, if it does, then it gets the last entry of FirstName.LastName.NN and adds FirstName.LastName.NN+1.

So do you mean I should be doing synchrinization between the HTTP Sessions? Hmmm, that sounds complex to me, I will have to go and read up on that.
 
Les Morgan
Rancher
Posts: 710
17
C++ Java MySQL Database Netbeans IDE Oracle Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, so you don't have a middle layer of business rules that all this is filtered through before hitting the DB, in that case I'd just let them hit the Exception, as you're doing now, then put them back in after a random wait.

Ahmed Bin S wrote:
Les Morgan wrote:Let me see if I understand:

You are sending them to the DB to check for the next session value for Joe Bloggs, both get the value 3 back.

I would look at synchronizing the code that does that step.


Well, each user will be registering through their own HTTP Session, and when the user clicks submit, the code checks if the FirstName.LastName exists in the database, if it doesn't, it creates a URL with FirstName.LastName, if it does, then it gets the last entry of FirstName.LastName.NN and adds FirstName.LastName.NN+1.

So do you mean I should be doing synchrinization between the HTTP Sessions? Hmmm, that sounds complex to me, I will have to go and read up on that.
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Les Morgan wrote:No, so you don't have a middle layer of business rules that all this is filtered through before hitting the DB, in that case I'd just let them hit the Exception, as you're doing now, then put them back in after a random wait.


Thanks for the reply, at the moment I don't.

Maybe I should first go spend a few weeks reading up instead of getting ahead of myself, and then if I have any questions, I will come and ask again.

Thank you.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ahmed Bin S,

First of all, a warm welcome to CodeRanch!

Ahmed Bin S wrote:This isn't technically a Java question, but as I am writing an application in Java and will be asking lots of Java related questions here over the coming weeks as I learn things and come across things I do not fully understand, I hope no one minds me asking this question here - saves me from having to sign up to a database forum!

That's no problem at all! You are more than welcome to post all your questions in (preferrably) the appropriate forum. If you CarefullyChooseOneForum, you are more likely to get a good answer to your question.

Ahmed Bin S wrote:My question is how would you handle this? My idea is that as this event is expected to be very rare, to simply have an exception block that catches this and tries to insert the next value instead. Would you agree, or is there a "better" way to do this? Good design is very important to me so I would like to learn about best practises here.

I (of course) don't have any idea about business requirements. But my first question would be if it's really required to have a unique index for every same name. Or is it ok to have just a unique index for every url? Then all the urls will be unique as well. But you miight end up with http://<domain>/Joe.Bloggs.123 and http://<domain>/Joe.Bloggs.401.
Second question: is it required to store the urls themselves in the database? Or is it also ok if the url (which seems to be a pattern) is generated at runtime based on database fields like id, first_name, and last_name?
Third and final (for now) question: what will happen if "Joe Bloggs" has submitted the form (resulting in Joe.Bloggs.2), notices he made a typo and his name is actually "Joe Blogs"? Is this a possible scenario? If it is, did you already consider it? How would you handle it?

Hope it helps!
Kind regards,
Roel
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:

First of all, a warm welcome to CodeRanch!


Thanks!

Roel De Nijs wrote:
That's no problem at all! You are more than welcome to post all your questions in (preferrably) the appropriate forum. If you CarefullyChooseOneForum, you are more likely to get a good answer to your question.


Great!

Roel De Nijs wrote:
I (of course) don't have any idea about business requirements. But my first question would be if it's really required to have a unique index for every same name. Or is it ok to have just a unique index for every url? Then all the urls will be unique as well. But you miight end up with http://<domain>/Joe.Bloggs.123 and http://<domain>/Joe.Bloggs.401.


Yes, we want to have a unique name for every same name.

Roel De Nijs wrote:
Second question: is it required to store the urls themselves in the database? Or is it also ok if the url (which seems to be a pattern) is generated at runtime based on database fields like id, first_name, and last_name?


The plan is that every user that registers will get a hidden id, uid (we will use a sequence for that), but they will also have a url bit that is FirstName.LastName.NN, and they can then give out this URL to people who can then visit their page, so I won't be storing the actual URL, but just the FN.LN.NN bit.

Roel De Nijs wrote:
Third and final (for now) question: what will happen if "Joe Bloggs" has submitted the form (resulting in Joe.Bloggs.2), notices he made a typo and his name is actually "Joe Blogs"? Is this a possible scenario? If it is, did you already consider it? How would you handle it?


That's a good question, I had overlooked that! Well, in this case, the inital name will always remain stored in the database, but a new entry will also be made.

So say Sarah Wood is the 10th person to register, in the database we will store:
uid, URL_id, is_current
10, Sarah.Wood.1, 1

Now say she gets married a year later and becomes Sarah James. In the database we will now have:
10, Sarah.Wood.1, 0
10, Sarah.James.1, 1

i.e. Sarah James is now the current_id.

We will however still redirect from the old URL to the new URL, so if someone typed in http://<domain>/Sarah.Wood.1, they will get redirected to http://<domain>/Sarah.James.1.

The reason I want to design it like this is so that people can give out the URL of their homepage, and they don't have to bother about giving out the new URL if their name changes etc.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ahmed Bin S wrote:The reason I want to design it like this is so that people can give out the URL of their homepage, and they don't have to bother about giving out the new URL if their name changes etc.

I see! And I definitely like the idea about the automatic redirects

But if people can give out the url of their homepage, would it not be much better if people can choose the name of their own homepage (instead of being limited to just FN.LN.NN). And if your homepage name is already taken, you'll have to look for an alternative name (and the website can provide some suggestions during registration). Just like how Twitter (and other social media) works.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ahmed Bin S wrote:So say Sarah Wood is the 10th person to register, in the database we will store:
uid, URL_id, is_current
10, Sarah.Wood.1, 1

Now say she gets married a year later and becomes Sarah James. In the database we will now have:
10, Sarah.Wood.1, 0
10, Sarah.James.1, 1

If 10 is the uid and generated using a sequence, the new entry will probable have another uid and thus makes it impossible to redirect like you've planned (as their is no relationship between uid of the old entry and uid of the new entry). So you'll probably have to adjust your database design a little as well.
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
I see! And I definitely like the idea about the automatic redirects

But if people can give out the url of their homepage, would it not be much better if people can choose the name of their own homepage (instead of being limited to just FN.LN.NN). And if your homepage name is already taken, you'll have to look for an alternative name (and the website can provide some suggestions during registration). Just like how Twitter (and other social media) works.


Actually, this is a good idea - Facebook do it like the FN.LN.NN (which redirects to FN.LNNN), but the Twitter model is much better IMHO!

And you're right about the sequence bit, I haven't got around to creating my data model yet, but that's definitely something I would have to consider.

Many thanks.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ahmed Bin S wrote:Actually, this is a good idea - Facebook do it like the FN.LN.NN (which redirects to FN.LNNN), but the Twitter model is much better IMHO!

Glad to hear you liked the idea!

And with the Twitter model you can opt for some alternatives:
  • no automatic redirects: once a user changes its homepage name, the old one is gone (and becomes available for other users)
  • automatic redirects for all: once a homepage name is used, it's unavailable and will redirect to the most recent one (that's the current Twitter model I believe)
  • keep a most recently used for every redirect. if it's not used for 6 months, the redirect is removed and that homepage name becomes available for other users
  • ...
  •  
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic