I'm new to SQL and could use some help in creating a database schema for my program, which manages and installs programs for my home network. Are there any guidelines/tutorials for creating database schemas?
+2
A:
Probably the most important concept to understand before you design your schema (you'll thank yourself for it later, trust me! :-) is that of Normalisation. The tutorial at db.grussell.org doesn't look too shabby and will give you a good grounding. In fact, if you click the "Up One Level" link and take a look around, some of the other information might be quite useful as well.
My "top tip" is: Write it down on paper or in notepad, or anything other than a database, before you start writing code. Get a good idea of what you need your schema to be able to do before you set it in stone (And by "set it in stone" I mean, realise that you've written a load of code against the schema that would have to be re-written if you change it to do what you've just realised you now need).
Rob
2009-08-25 09:00:47
If i understand it correctly, these are the first 3 levels of normalization:first normal form means that there are no database fields with more than one item in your tablessecond normal form means that if you have a composite primary key all other fields are fully dependent on the entire key not just parts of it in each tablethird normal is where you have no non-key fields that are dependent on other non-key fields in any table
RCIX
2009-08-25 09:38:25
Don't worry too much about learning all the formalities if you don't absolutely need to - get a good idea of the principles and go from there. 90% of normalisation (as with a lot of other database related "stuff") is common sense once you get a feel for it. That said, it sounds like you're making a good inroad into understanding it all! =)
Rob
2009-08-25 10:49:21
Thanks! i'll get working now.
RCIX
2009-08-25 21:09:24
+1
A:
Designing Databases is a separate field of study and expertise. It cannot be condensed into one answer. Since you are interested in tutorials, look at the section on Database Design in any text book on Database Management Systems. I would recommend Database System Concepts, 5e, Abraham Silberschatz, Henry F.Korth, Sudarshan
In database design, remember the following
1) You are identifying the important objects of interest in your home network. Try to avoid excessive indulgence in the processes themselves though they are important to identify the important data units that you need to capture
2) Use ER/UML modelling techniques to come up with a Data Model Diagram/design. There are many case tools that can help you in drawing this.
3) Use the principles of Database Normalization to fine tune your schema to avoid data redundancies. Redundant data leads to the following side effects: Inability to maintain consistency among redundant data, Inability to store some data in an elegant manner
3) Forward engineer your design to DDL statements for the DB of your choice. Most case tools support this.
Case tools:
- Microsoft Visio
- ER Studio (very expensive)
- TOAD data modeller
There are many open source tools too. You can try Dia. This does not support forward engineering
bkm
2009-08-25 09:30:33
i'd like to point out that i'm merely a beginner and i only want enough knowledge to build a decent database to store information for my program. That said, you do have a good answer here!
RCIX
2009-08-25 09:41:35