I am a junior. developer (5 several weeks at work), and I've got a question about data normalization. Now, when i comprehend it, the overall principle behind data normalization is to produce a RDBMS where data redundancy is stored low. During my project, among the DB people produced a DB. We've 50+ tables, and also the tables within the DB are often very fragmented, ie. a table has 2 or 3 posts and that is it. Now, if this involves writing sql queries, it is something of the minor hassle since each query involves brushing through a number of different tables and joining them together. I'm wondering if this sounds like a an unwanted effect of information normalization? Or performs this indicate another thing?
I understand the simplest factor to complete, for me personally, is always to write tables based from the queries I must write. This can produce a DB with many different redundant data, however i was curious if there's a contented medium?
Just like a postscript, I'd rather not run into like I am whining about my work, but I am truly curious to explore this. My work atmosphere isn't the most friendly and so i don't feel at ease appearing this with my co-workers. However, I'd appreciate any ideas, books, lessons or opinions from more knowledgeable people.
general principle behind data normalization is to produce a RDBMS where data redundancy is stored low.
Only partially true.
Normalization isn't about "redundancy".
It comes down to "update anomalies".
1NF may be the "avoid using arrays" rules. Breaking 1NF means a row is not atomic, but an assortment and independent updates within the collection wouldn't exercise well. There'd be securing and slowness.
2NF may be the "one key" rule. Each row has exactly one key and my way through the row is dependent around the key. You will find no dependencies on part from the key. Some people prefer to discuss candidate secrets and natural secrets and foreign secrets they might exist or they might not. 2NF is content when all characteristics rely on one key. If the bottom line is just one-column surrogate key, this normal form is trivially satisfied.
If 2NF is violated, you have posts which rely on a part of a vital, although not the entire key. Should you have had a table with (Part Number, Revision Number) like a key, and characteristics of color and weight, where weight is dependent overall key, but color only is dependent around the part number. You've got a 2NF problem enabling you to update some part colors although not others, creating data anomalies.
3NF may be the "just the key" rule. Should you put derived data consecutively, and alter the derived result, it does not match the origin posts. Should you change a resource column without upgrading the derived value, you've got a problem, too. Yes, triggers really are a bad hackaround to permit 3NF design violations. That isn't the purpose. The thing is basically to define 3NF and reveal that it prevents an update problem.
each query involves brushing through a number of different tables and joining them together. I'm wondering if this sounds like a an unwanted effect of information normalization?
Here's what's promising: it's not necessary to use the stabilized tables! ... It's very easy (a minimum of for DBAs) to produce an abstraction layer of became a member of sights on the top from the stabilized data tables, putting the bottom tables completely "behind the curtain", and from sight.
It may sound like data normalization, however i would need to learn more concerning the schema, the company situation, etc to create that call dependably. Should you have had charge of the database, you can write a view that signifies common queries that link tables. To be able to increase performance, you can create an indexed or materialized view (the title is dependent around the database platform, within this situation, Oracle versus. Sql Server).
Nearly any database primer can help you together with these concepts. If you work with Sql Server and therefore are really thinking about learning more, SQL Server Books Online is a superb resource.