Databasing/ SQL Newbie in need of guidance

[M0KUJ1N]

Hi guys,

I've decided that as databases aren't an area of computing that I have a great deal of experience or knowledge of that I need to do something about this state of events.

So I've decided to take up a small web-database project which involves transferring our skills matrix, which currently sits in a tabular form into some form of database, so that is is more easily managed and updated as team members join/ leave or as applications change.

The table (as it stands) I've copied to:

http://homepages.ed.ac.uk/mcairney/skills.html

Now (as I understand it) this is a NxN relationship so will need 3 tables. I've knocked together the following as a first attempt:

USERS(UID Name Username)
APPLICATIONS(App_ID App_Name App_Class)
SKILLS(Table_ID Name App_Name Level)

(Ive used bold for Primary Keys and Italics for foreign keys)

Now, am I going about this the right way and is this design correct or is there room for improvement? Tbh I want to get the design correct before actually getting on with the task of producing the database in MySQL and coding

 

[M0KUJ1N]

That's good, but if you've created the UID and App_ID keys, use them instead (well, you don't really have a choice), and in the skills table you should have a multi-part key consisting of Table_ID, UID and App_ID

Edit: Also, I'd create a table for the AppClass with a primary key CID (class ID?) and I'd do the same with level, so then you simply refer to numbers in the tables which makes them smaller and also allows you to alter the name of the classes/levels by updating ony one row in the respective table.

Database normalisation is all about "The key, the whole key and nothing but the key"

Edit2: Of course you could always just store the level as a digit (use an ENUM field to restrict exactly what can be stored I'd guess) and then write the related word (Used, Expert etc) out in your code. That'd cut out my silly point of wasting another table for it. Same may also apply to the app classes, upto you really.

OK thanks for this, a relief that my first attempt wasnt totally off the mark but nice to hear that there are improvements that can be made at this stage before I go ahead and implement it

I see your point re: using the UID and App_ID, as these are *guaranteed* to be unique values. I assume that PHP would allow me to output the Name and the AppName fields that relate to these values from SQL queries even if they are not the values in the "skills" table?
I was also considering pulling the UID and username fields from AD via LDAP but thats probably going to be running before I walk in this instance