2

I have a java swing application that interacts with MySQL and i want to be able to create license key that allow the user to use the application, the user can only have one license number, here is what i have so far in SQL.

CREATE TABLE User (
    id INT AUTO_INCREMENT NOT NULL,
    License VARCHAR(100) NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE Address (
    id INT AUTO_INCREMENT NOT NULL,
    LicenseNumber VARCHAR(255) NOT NULL,
    FOREIGN KEY (LicenseNumber) REFERENCES User(id),
    PRIMARY KEY(id)
 );

Is this the correct solution? also what would be a good way to generate the actual License Number with SQL?

This is my solution to my question: Here is the SQL:

CREATE TABLE User(
   id int NOT NULL auto_increment primary key,
   LicenseID VARCHAR(100) NULL,
   CONSTRAINT fk_license_number FOREIGN KEY (LicenseID) REFERENCES License(LicenseNumber)
 );

CREATE TABLE License(
   id INT AUTO_INCREMENT NOT NULL,
   LicenseNumber VARCHAR(100) NOT NULL UNIQUE,
   PRIMARY KEY (ID)
);

For the LicenseNumber, i went ahead and used sha1 in my java code Java String to SHA1

Community
  • 1
  • 1
Rick
  • 12,606
  • 2
  • 43
  • 41
  • No. If `Address.LicenseNumber` is a reference to `User.id`, it should have the same type (`INT`), and be called something like `user_id`. Not `LicenseNumber`. And SQL is used to query the database. Not to generate license numbers. – JB Nizet Nov 10 '13 at 22:08
  • Can you elaborate what purpose did you have in mind for `Address` table? – peterm Nov 11 '13 at 00:54

1 Answers1

0

You should rather use license id as a Foreign Key rather than a license number. Therefore schema should rather look like

CREATE TABLE License
(
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  LicenseNumber VARCHAR(40) NOT NULL UNIQUE
);
CREATE TABLE User
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  LicenseID INT, 
  CONSTRAINT fk_license_number FOREIGN KEY (LicenseID) REFERENCES License(id)
);

If you need to you can generate SHA1 on db-side by using SHA1() function. E.g.:

INSERT INTO License (LicenseNumber) VALUES (SHA1('Lisence1'));

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
  • @Rick You're quite welcome. If you feel like the answer was helpful please, consider to mark it as accepted. **[Here is how you can do it](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work)**. – peterm Nov 11 '13 at 06:53