0

Background : I have a database table called Contact. All users of my system have details of their contacts in this table including a firstname, a lastname, and also a varchar field called 'UniqueId'. Users of the system may put anything in the UniqueId field, as long as it is unique from that user's other contact's unique ids.

Aim : I now need to change my code so a unique id is automatically generated if the user does not provide one. This should be short and visually pleasing. Ideally it could just be an auto-incrementing number. However, AUTO_INCREMENT works for an integer field, not a varchar field.

Also note that each contact UniqueId needs to be unique from the other contacts of that user, but not neccesarily unique to the entire system. Therefore, the following UniqueIds are valid :

Contact
    UserId  Firstname Lastname UniqueId
    1       Bob       Jones    1
    1       Harold    Smith    2
    2       Joe       Bloggs   1

Question : So, how can I achieve this? Is there a reliable and clean way to get the database to generate a unique id for each contact in the existing UniqueId varchar field (Which is my preference if possible)? Or am I forced to make Java go and get the next available unique id, and if so, what is the most reliable way of doing this? Or any alternative solution?

Edit - 11th April AM: We use hibernate to map our fields. I'm just beginning to research if that may provide an alternative solution? Any opinions?

Edit - 11th April PM: 2 options are currently standing out, but neither seem as ideal as I would like.

1. As @eis suggests, I could have an auto-incrementing field in addition to my current varchar field. Then, either when a contact is saved the int can also be saved in the varchar field, or when a contact is retrieved the int can be used if the varchar is empty. But it feels messy and wrong to use two fields rather than one

2. I am looking into using a hibernate generator, as discussed here. But this involves holding a count elsewhere of the next id, and Java code, and seems to massively overcomplicate the process.

If my existing uniqueId field had been an int field, AUTO_INCREMENT would simply work, and work nicely. Is there no way to make the database generate this but save it as a String?

Improve this question
5
  • um. so uniqueid is only unique from the other contacts of that user. so what happens when two users that didn't use to be contacts, and chose the same uniqueid, contact each other? Commented Apr 10, 2013 at 15:56
  • A user has lots of contacts who he communicates with. In other words, its his own address book of contacts. A user never communicates with another user, and a contact never communicates with another contact. Does that answer your question? Commented Apr 11, 2013 at 8:10
  • Yes. But I don't think even if your field would be an int that AUTO_INCREMENT would work, as it can always collide with something that some other user has. Autoincrements don't do any checks. Commented Apr 11, 2013 at 17:20
  • Oh really? Thats a gap in my knowledge of AUTO_INCREMENT, then. :( Commented Apr 12, 2013 at 8:13
  • checks against user-supplied data, I mean. It doesn't collide with its own values, but nothing prevents some user giving a value that collides with AUTO_INCREMENT values. Commented Apr 12, 2013 at 9:23

2 Answers 2

Reset to default
2

I think what you really should do is ditch your current 'uniqueid' and generate new ones that are really unique across the system, being always autogenerated and never provided by the user. You would need to do separate work to migrate to the new system. That's the only way I see to keep it sane. User could provide something like an alias to be more visually pleasing, if needs be.

On the upside, you could use autoincrement then.

Ok, one additional option, if you really really want what you're asking. You could have a prefix like §§§§ that is never allowed for a user, and always autogenerate ids based on that, like §§§§1, §§§§2 etc. If you disallow anything starting with that prefix from the end user, you would know that there would be no collisions, and you could just generate them one-by-one whenever needed.

Sequences would be ideal to generate numbers to it. You don't have sequences in MySQL, but you could emulate them for example like this.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thanks for your input eis! Ideally, I would love to ditch 'uniqueId', but current customers like being able to enter their own unique id. For example, if the 'contact' is a student at a university, they can put the students 'student id' in the uniqueId field, which is very useful. Mmm, but you are making me consider having a seperate 'auto-generated' id field, which is used onscreen if the varchar unique_id field is blank ... but that sounds very messy!
@AndyA customers could put that in as an alias/custom id. You could still have your own autogenerated id within your app that is always generated. Customer alias could be then unique to that customer only. That's not at all messy if you compare it to what you have now, to my opinion.
so in practice you would use your current uniqueId as what I was speaking about with an alias, but the real id would be autogenerated id field, and 'uniqueId' would only be used by customers if they want to.
I fear that having 2 visible fields rather than 1 would seem over-complicated to a user. The current uniqueId field, if populated, is used as an id which user's can reference to update details about their contacts. This would need to stay the case. To have potentially 2 id fields which do the same job would seem overkill compared to simply having 1 field that gets generated if left blank. If this is possible somehow, I believe its the way I should go.
I did not say the two fields should be visible to the user, that's up for you to decide what gets shown and how. I was talking about database design. What gets shown to the user is completely separate topic.
|
0

I apologize, I really don't know MySQL syntax, but here's how I'd do it in SQL Server. Hopefully that will still have some value to you. Basically, I'm just counting the number of existing contacts and returning it as a varchar.

CREATE FUNCTION GetNewUniqueId
  (@UserId int)
RETURNS varchar(3)
AS
BEGIN
  DECLARE @Count int;

  SELECT @Count = COUNT(*) 
  FROM Contacts 
  WHERE UserId = @UserId;

  SET @Count = @Count + 1;

  RETURN CAST(@Count AS varchar(3));
END

But if you really want something "visually pleasing," why not try returning something more like Firstname + Lastname?

CREATE FUNCTION GetNewUniqueId
  (@UserId int, @FirstName varchar(255), @LastName varchar(255))
RETURNS varchar(515)
AS
BEGIN
  DECLARE @UniqueId varchar(515), @Count int;

  SET @UniqueId = @FirstName + @LastName;

  SELECT @Count = COUNT(*) 
  FROM Contacts 
  WHERE UserId = @UserId AND LEFT(UniqueId, LEN(@UniqueId)) = @UniqueId;

  IF @Count > 0
    SET @UniqueId = @UniqueId + '_' + CAST(@Count + 1 AS varchar(3));

  RETURN @UniqueId;
END
Improve this answer

3 Comments

how does that prevent the situation where somebody chose '33223' as their uniqueid at the very beginning? I don't see any checks for that.
@eis You're right, OP's system opens a pretty sizable can of worms. I don't think there is a solution to that issue without getting stuck in a possibly endless loop. I agree that your answer is the best way to go, but I did want to illustrate at least a starting point if OP wants/needs to keep their original setup.
Thanks Jeff. In regards to your first solution, as both you and @eis have already pointed out, it's probably not ideal. In regards to your second solution, thats actually similar to what we have recently done, but to ensure uniqueness, we've had to use 'firstname+lastname+phonenumber', which is not visually pleasing at all. And we can't just use 'phonenumber', because the contact might not have a phone number. Basically, I'm stuck with a legacy system that is not ideal. Still, do you or anyone have any more ideas?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.