I'm hoping somebody can help me... There is something i can not get my head around and its becoming quite frustrating. I'm currently trying to teach myself how to create database driven websites using php & mysql. The main problem that i can't seem to resolve is the planning of a database. No matter how many times i read about relationships and normalization, when it comes to creating my own database, i just seem to confuse myself and it feels like i'm over complicating things. I am wanting to store information in a database for a furniture shop. The shop deals with different manufacturers in all types of furniture, such as bedroom, dining, lounge etc... it is a personal project so i'm not sure whether i'm setting myself a target that is too high. It is just to try and get an understanding of database design.... If somebody could shed some light onto my situation or point me in the right direction of some good references to learn this sort of thing id be very appreciative because at the moment it feels like im going round in circles. Thank you Neil
Hi Surrey, It's not so much the sql queries that i'm having an issue with, its more of the actual planning of the database. Thank you anyway though Neil
The key to planning a database is avoiding repetition of information and having one or two fields within each table which will 'link' information between tables. For example if you have a basic ecommerce site then you usually start with a customer_id field then further info fields such as name, address etc. Then you'd have product tables with product_id - your unique field for the primary key and further product information such as product name and product description. Where it all links together will be in the order table. This will have a unique order_id, will contain a customer id and will also have product ids for each order line. This way you keep storage to a minimum and you're not repeating information storage. When you pull out the data then you'll need to pull out information from multiple tables and make sure the query contains something to the effect that the customer table's customer_id matches the customer_id in the order table so that only linked records are found ie you only list customers with orders. Does this make sense?
I think srichards covers most of it, and the only thing I would add is about redundancy. You need to try to avoid empty fields in your tables. I usually just throw things into a bunch of tables and then look to see if every record will require an entry in every field of the table. If you start to see lines with empty fields then you should move those fields to new tables and reference back with an id.
