We've been given our final assignment at uni for the database module I'm taking, and we have to build a working database. The database can be for any purpose, but it needs to be relational, we have to show our design processes (entity relationships, normalisation etc.), and hold regular scrum cycles.

 

We are free to use whichever database system we want. Some of the other guys on the course are going with Access, but I took an introductory course last year and used Access, so I want to try something new. So I'm looking at using MySQL, and creating a web based UI using php. I've chosen to build a media database (personal Vinyl, CD, DVD, MP3, MP4, AVI collection).

 

I've been doing a fair bit of reading up on PHP and MySQL, but I was wondering if anyone has any tips & tricks that they wouldn't mind sharing? Especially with the normalisation, but anything would be very welcome.

 

Thanks in advance,

 

Stu

Edited February 26, 201115 yr by StuartPB

1. phpmyadmin will likely be your best friend

2. MySQL Workbench will be your best friend's sister

 

Just check both those softwares out. Do you know about normalisation etc.? It's very nicely explained on Wikipedia and other places.

1. phpmyadmin will likely be your best friend

2. MySQL Workbench will be your best friend's sister

 

Just check both those softwares out. Do you know about normalisation etc.? It's very nicely explained on Wikipedia and other places.

 

Thanks for the tips Andy, I'm pretty familiar with phpmyadmin, but haven't had much experience with MySQL Workbench, so I'll have to get my head round that

Yeah, we've had a few lectures on normalisation, I'm on 2NF at the mo with the data. We've been told that 3NF will be acceptable, but we could receive a few more points on our grade if we try for 4NF.

You may want to use foreign key constraints - but you can only do this if you create the tables with the InnoDB engine, rather than the default MyISAM. InnoDB also has a proper implementation of transactional sessions and the benefits of row-level (rather than table-level) locking.

 

Foreign keys are useful in relational databases. For example, say you have catalogue items in various categories, then FK constraints enforce the "sensible" rule that you cannot delete a category if there are items still remaining in it.

 

Another scenario is establishing a hierarchy of tables - for example, you have a generic Items table listing all your catalogue items, and various specific subtables which provide supplementary information, like Books which contains ISBN number, author and title fields. A FK constraint can be imposed so that if you delete the item from the Items (parent) table, then the same entry (identified by matching foreign key) in the Books (child) table is also removed automatically.

 

Depending on what you're doing, FKs may be useful.

That's great advice. Thanks, I really appreciate that. I'll definitely be using foreign keys within the database so the advice on using InnoDB over MyISAM has saved me some headscratching

It'll also give you more to talk about, in the un doubtely huge report they will make you write

 

Yeah, I'm not looking forward to that part of the assignment.