[Wolves] MySQL

Andy Jewell Andy.Jewell at sysmicro.co.uk
Mon Aug 2 10:24:44 UTC 2010


Hi Pete,

I'm fairly au fait with MySQL 5.x... I look after replication clusters and mysql drbd clusters, doing all the normal admin stuff (index optimisation, backups, restores etc).

Your DB plan looks ok but you have no primary keys. You also need to have an idea of the relationships between tables - how they link together and the type of link (one-to-one or one-to-many etc). Where tables have a one-to-many relationship, you need to have a column in the subordinate table that contains the key of the master table. You also need to decide the TYPES of each column, i.e. String, Numeric, Boolean etc.

Your Customer table seems fine - I'd just add: 
Primary Key: CustomerID

The Product, Type, Licenses and Dates tables really just seem to be examples of the types of things you might store in there, not the table structures themselves. I'd do:

Product - a list of products
===================
ProductID (Primary Key)
Name - short name for product, e.g. "Sendmail"
Description - Longer description, e.g. "Sendmail mail transport agent"
License - What license it uses, e.g. "Sendmail license (Open Source)"
Supplier - Where to get the software, e.g. "sendmail.org"
Type - Type of software, e.g. Home, Business, Windows etc. chosen from the Types table
Category
Version
Platform
etc.


Types - a list of allowed software types
============================
TypeID (Primary key)
Name - Type name, e.g. "Home"

Licenses - a list of existing licenses for software
==================================
LicenseID (Primary key)
ProductID (Foreign key) - links back to Product.ProducID
Name
Contact
Expires
Cost

Hope that helps!

Andy D'Arcy Jewell
SysMicro Linux Support

T:  +44 (0) 844 991 8804
M: +44 (0) 7961 605631
F:  +44 (0) 844 357 7020
E:  andy.jewell at sysmicro.co.uk
W: www.sysmicro.co.uk

CRN THE CHANNEL AWARDS 2009 WINNER
SysMicro named CRN’s Editor’s Choice for Emerging Business of the Year 2009, recognising SysMicro for our considerable growth, specifically in Enterprise Solutions.
________________________________________
From: wolves-bounces at mailman.lug.org.uk [wolves-bounces at mailman.lug.org.uk] On Behalf Of Peter Cannon [dick_turpin at archlinux.us]
Sent: 02 August 2010 10:48
To: Wolverhampton Linux User Group
Subject: [Wolves] MySQL

Hi All

So who are the MySQL Guru's here? I know Dave Goodwin is but is there
anyone else?

I want to start learning it properly I can do the simple stuff create a
db and tables view etc but that's about it. I have a couple of books but
they are for V4, anyone interested in answering my 2 Bazzilion idiotic
questions potentially off list?

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?

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?

I know I can do some tutorials, its a bit easy copying out of a book I
fancied trying myself.

Cheers.

_______________________________________________
Wolves LUG mailing list
Homepage: http://www.wolveslug.org.uk/
Mailing list: Wolves at mailman.lug.org.uk
Mailing list home: https://mailman.lug.org.uk/mailman/listinfo/wolves



More information about the Wolves mailing list