[Wolves] MySQL

[Peter Cannon]

On 02/08/10 11:26, Alex Willmer wrote:

> - 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.

Hm I think you are right, I take it the account_no would be unique to 
the customer? so cardboard boxes on ACC 123456 would relate to A-Company 
the same as Bic Biro's on ACC 123456?
e.g if you called up ACC 123456 in theory you'd see A-Company = 
Cardboard boxes and Bic Biro's?

> - For products you've switched from listing fields, to listing
> records.

Ah so that's wrong then is it?

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.
>

I sort of see what you are saying I'll need to read it a couple more 
times to digest it, I'll probably come back to this mail in a while.

I have updated the example I gave maybe people can have another look?

Does every table have to have a unique ID then?

Basically its a simple database of all ESET anti virus that I do for 
customers. I currently use Keepass for storing the data, it works OK and 
I can record other stuff such as email accounts, FTP etc.

I just thought I'd use the ESET as its fairly simple "This Customer 
bought this product for this many users it expires on... and the 
username and password is..".

The look up criteria would be "who expires when" "people with 25 or more 
licenses" blah blah blah. I'm not looking for it to be written for me 
more a sort of "You're heading in the right direction" "Pete that's wrong".