[Wolves] MySQL

[Alex Willmer]

On 2 August 2010 10:48, Peter Cannon <dick_turpin at archlinux.us> wrote:
> Question 1
> So my book says "Plan out your db before starting to create it" as a project
> I have done this
> http://www.cannon-linux.co.uk/downloads/Stuff/Eset_db_structure.ods does
> that look OK as a structure?

A few pointers

- What uniquely identifies a customer? For instance will you have more
than one account with the same company? I'd suggest an account_no
field, to use as the primary key.

- For products you've switched from listing fields, to listing
records. Other than the name what do you want to record about a
product? Depending on your mood/use some or fewer of the following
might be appropriate:
    Products: product_no, product_name, product_start_date,
product_end_date, product_cost
or
    Products: product_no, product_name, product_is_discontinued,
product_replacement
you might also want a concept of maintenance period/cost.
- I would call 'type' instead 'platform', type (like class) is a very
overloaded word.
- In order of increasing initial complexity, robustness and purity -
you could deal with platforms by
  - Mentioning it in the product name (e.g. 'Foo for Linux/BSD/UNIX')
  - A field in the product table (e.g. product_platform="Linux,
Windows, Unix, OSX") or
  - As a full one-to-many relation (Platforms: platform_no,
platform_name; Product_Platforms: product_no, platform_name)

> Question 2
> Under Licenses and Dates would I need more than one field given that you
> could have more than one product purchased at different times?

Something like
Purchases: license_no, purchase_date, customer_no, product_no,
license_seats, license_expiry_date
though that might cause an issue when a customer wanted to renew their
license, if they got a new license_no to key into all their installed
machines.

-- 
Alex Willmer <alex at moreati.org.uk>
http://moreati.org.uk/blog