Database structure with shorthand links

[gaptrast]

I have a site where users can use an app where everything they do is stored in values. I want to let users save what they have done, and share it with a generated link. Like example.com/gUy87f
The values will be stored in a database, and somehow the unique string (gUy87f) attached to them. So when a user loads the page (example.com/gUy87f) all values show up in the web app.
I have seen this at jsfiddle.com. When you save a document it automatically makes a unique string for you. Also, when you update the document, jsfiddle will add an auto incremeting /1 after the unique string. like example.com/gUy87f/1, example.com/gUy87f/2 and so on...

I am very fresh when it comes to SQL and databases, and don't know everything about it. Therefore I have some questions:

• Should the data expire after a certain amount of time or when it has not been used? Does it expire at jsfiddle (etc)?
• How should my database be structured? It is fairly simple data. Should it be like this: id || uniquestring || data1 || data2 (where id is 0,1,2,3..) ?
• Is it a good idea to let users create as many copies and saves on my server as they want? Is that something to be afraid of? Do the proffesional websites do something that I should do too?
• What should I google for when searching for this (like shorthand pagemaking)?

 

[caftpx10]

1. It's your choice. It's going to remain in the database either way, unless you make it so it gets deleted.

2. You can make 'id' have A_I (checked). The auto incremental count by default starts at 1 (first column).
The second one could be 'uniquestring', and third 'originalstring'. You can add a forth one holding the UNIX timestamp so that you can do the expiration thing if you wish.

3. It depends. If you want to count how many URLs have been created then you may want to just store it.
You can check if that same exact URL exists in the table and grab the unique string from that original match for the user to use, if you want.

4. I think you can search for 'link shortener' (if that's what you mean).

 

[leafypiggy]

I'd do this by storing each individual record as a uuid, and then using the first few bytes as the "unique" string (since that's what a UUID is..)

I'd store that in a table with that as the primary key, and then a few other columns (time_created, time_updated, etc) and whatever other info you need (maybe a reference/foreign key to a user ID to show it belongs to a user?)

I'd have another table with the "content" of whatever you're storing, with a foreign key to the previous table that holds the ownership info about the "content". When someone requests version "2" of the content, you grab all the data in the table matching the UUID, then grab the second one. (or third, if it's zero indexed..)

 

[Skizzerz]

the mysql UUID() function provides a 36-character uuid string that you can use with insert statements to generate a unique identifier. The column you are storing the value in should either be a char(36), binary(36), varchar(36), or varbinary(36) -- fixed length is preferred over variable length since the size of a uuid is fixed, so you save a byte of storage by not having to specify how long the thing is. If you plan on using the uuid as a primary key referenced by other tables, binary is preferred over char because binary lookups/comparisons are faster than their string counterparts.