CODEWITHSUNDEEP
mysqldatabaseinnodb
Kay van Bree
Kay van Bree

Reputation: 2082

Handling versions

Currently I'm working on a website for a client, who deals in software plugins. The client needs to be able to upload Products, and for these products different Versions, Updates and Patches. For example: veProduct v1.1.4 is Version 1, Update 1 and Patch 4 of the Product veProduct. Customers need to be able to buy a License to a Version. The License is a file that's generated per user, wich needs to be available for download. Currently I'm designing the database for the website, and I stumbled into a problem. How should I handle the different patches & versions?

My current Design:

In this design I created a table Product, which contains the information of the product itself, like the name and the description. Of the Product there can be multiple versions, which require different licenses, so I created another table called Version. In this table there will be a download-link, a changelog and the pricing of this version of the product. Also a discount price for customers who own the license of an older version, but that's not important. After this I created the User table, so that I could link the user and the version in the table License. In this table you can also find the download link to the license file.

After this, my trouble starts. I created the table Update, which is for example v2.3. This means it's version_id is 2 and it's update_id is 3. Then I created the table Patch, which could be for example v2.3.1, where version_id is 2, update_id is 3 and patch_id is 1. Until now there's no problem, but there's one big flaw in this design. That's when I want to upload a patch for a version that has no updates yet, like v1.0.1. That means I have to create a record in the table update, with the update_id 0 and no download link or changelog. I don't want to create a record without any purpose but to be a patches 'parent'.

[deleted]

Fixing my problem:

I find it hard to think of a solution, so I ask your help.

Would it be a good idea to get rid of the download-link fields in the version and update table and rely on the ones in the patch table? This way I would always make another patch, even if I only upload a newer version, or if I upload a completely new product. It just feels wrong to have the update table, only for being a patches parent.

So, can anyone help me figure this one out? How should I store new products, versions, updates and patches in my database. Where should I store my download-links? And last, but not least, how can I keep my version linked to the customer, and to the download-link for the license? (So every user gets his own license per version of a product, not per product, update or patch)

Sincerly,

Scuba Kay

PS: It really sucks not being able to post pictures if you're a new user, 'cause I'd like to post a screenshot of my MySQL Workbench instead of a bulk of code.

Upvotes: 1

Views: 103

Answers (1)

Chris
Chris

Reputation: 23171

Instead of Update and Patch, why not just have a Release table that looks like this:

release_id, 
version_id, --foreign key 
type, -- this is either UPDATE or PATCH
release_name, -- this is the full ver number 1.0.1, for instances
changelog,
download_link

This would allow you to keep version so you can associate that with customer for licensing purposes but would let you be flexible about what you create first (updates or patches) without having to insert dummy rows.

BTW: In this model, I'd remove the download link from Version so it only exists at the release level (essentially release 1.0.0).

Upvotes: 1

Related Questions