or 02-9262-7770 
| An Introduction to Databases | |
By Chris Smith |
Published
12/13/2005
|
Programming
|
Rating:
 
|
|
|
Introduction to Database Design - Step 2Comments will need a reference to the news item they are commenting on, the actual comment (of course!) and when the comment was made. You might want to keep the name of the person making the comment, their email address and a url for their website. For our example, only existing authors will be able to comment. The answers will tell us exactly how your CMS will work and how the database will need to be designed, so it's quite important to get as much information as you can at the start. Even though you haven't started yet, you may also need to consider "feature requests". If you hear a suggestion about putting news in multiple categories (you may hear "I'd like to be able to do this but I don't need to right now"), build the database with this in mind. It may add a bit of extra work now, but it's easier to do at the start than it is later on when you have to redesign everything! How you design the database can affect speed as well. A poorly designed database is very difficult to use, add features to, or speed up. If you have to do complicated queries or have to perform complex calculations to work out searching, it's going to be hard to speed this up. As you can see, database design can get quite difficult, the key is not to rush it and try to think about everything you need to do before you start. Brainstorm with others about what you might need, get them to take a look at your feature lists and questions and see if you're missing anything. Once that has been decided, you can start looking at the table structure. Using the news table as a starting point, we need to keep the following information: - title - content - author (we're only going to allow one author) - approved - category (we're only going to allow news in one category) The first thing to look at is how can I uniquely identify a particular row? You need to be able to uniquely identify a particular row if you want to update or delete it, we don't want to accidentally remove the wrong entry. A 'title' for a news item doesn't uniquely identify a row because you can have multiple items with "New Product Update" or "Special Discount" and so on. Going through the news items, we can't use any of these fields to uniquely identify a row. What next? We can make one up! In mysql we can use an 'auto_increment' field, in postgresql it's a 'serial' field. This field will allow us to uniquely identify a row and will automatically change so we can basically "forget about it". It is a number that gets updated each time a row is added to the database table. This becomes our primary key (by definition a primary key simply allows us to uniquely identify a row in a table). Other examples of primary keys are social security numbers and ISBN's for books. Only one person has a particular SSN and only one book has a particular ISBN. A phone number would be no good as a primary key - you could have multiple people listed under the same number. An email address could be used as a primary key but it depends on the rest of your database structure (for example, family members might share the same email address, so using this depends on what you are trying to achieve). |
|
