Database design

NerfNerder

Hey guys. Me again with another question. I need to design a database for a project I am trying to get done for college. The thing is I am terrible at actually designing software. Programming I can handle once I know exactly what I need to do. Do any of you know where I can get some good tips or help for doing this. I'm starting to seriously stress!! Thanks.

NeverSayDie

Unfortunately, experience is the only real way you get good at data modelling - it does get (relatively) easier the more of it you're exposed to, so stick with it.

That said, googling for "database design" will turn up a variety of articles and tutorials on the topic, should be useful. Otherwise, a book like Database Systems (pretty standard, and weighty, text for a lot of 3rd level courses on the topic) might help too;
http://www.amazon.co.uk/Database-Systems-Implementation-Management-International/dp/0321210255

Data modelling (for normal relational databases anyway) works in terms of entities and relationships, so you need to figure out what entities you have in your system, and then what the relationships are between them. For instance, in a university records system, we might have students and lecturers, with a table for each. Then we need classes, so we'll add a table for that. Now a Class has a relationship with a Student (who takes one or more of them) and a Lecturer (who teaches one or more of them), so we'll have a relationship between them, expressed by adding a LecturerID to the Class table, and adding a StudentClass table, linking StudentID's with ClassID's (as that's a many-to-many relationship, you'd store it in its own table). That's a pretty basic example, but that's how it works - figure out what your entities are, and then start thinking about how they relate/link to each other.

In terms of keywords, along with the main entity-relationship notion, "normalisation" is a pretty important topic in relational data modelling. Reading up on that a bit should get you thinking along the right lines.

Mikia

Hi,

a database can be a very complex datastructure.
what you need to consider are both the physical structure (record lengths, formats, block size, hex/decimal/ascii/ebcdic) and the logical rules governing the data. The most common DB these days is a relational database (See: Oracles databases with tables, schema etc.

What i'd recommend as a start would be to leaf through Database Systems by Thomas Connolly and Carolyn Begg to get a feel for the datastructure and then look at IBM's DB2, Oracle 10, and some database interaction software like CICS before trying to implement a simple self-made version.

croo

while I agree with the previous posters, check out Len Silverston's "Data Model Resource" series of books to get a head start on what good designs look like!

Merrion

I find that the best tool for database design is - a whiteboard!

Draw out your entities and their relationships (1 to 1, 1 to many, many to many) and then eliminate any many-to-many with intermediary tables and eliminate any chasm traps....you should end up with a workable design pretty quickly.

kayos

Surprised no one has mentioned ORM and no its not object relational mapping as most people know ORM.

I find it a great way to model up databases and the natural english used can be very handy when trying to explain things or get information out of non technical business people. But any way its just another way of doing modelling.

To be honest it all depends on your project what you will find best. The main things to do is normalise as neversaydies said.

To expand on neversaydies example you really should have a Person Entity to store the attributes of a person, a student entity to store student specific attributes and a Lecturer entity with just the attributes that relate to a lecturer. Other wise you have multiple tables with same name, dob, gender and if a student then becomes a lecturer you have two copies of that Person between teh student and lecturer entities. But if you have a Person table only one copy exists which is used by both the student and lecturer entities.

qwytre

Mikia wrote: »

Hi,

a database can be a very complex datastructure.
.

If your database is very complex it is likely you haven't quite got your design right, you need to keep refining it until it is quite simple.