June 3, 2006 at 9:30 am
I've done my database studies and know about normalizing, relationships, primary keys and so forth. However, when it comes to actual developing of web sites, I've always skipped setting up relationships, and at times, the primary and (especially) foreign keys.
Now I'm designing my first db in Asp.Net 2, and the thought struck me - Why should I do this?
I guess the reason for setting up relationships is that the website should not get any trouble if rules are violated or poorly written, that is, data is input in an Orders table but not the Oredered_products table or the like. The only reason I can think of for using primary keys (when not defining relationships anyway) is to get the Insert, Delete, and Update statements of GridViews etc to work.
Or am I missing something? For many years, I've been told that the keys plus relationships taken together speed up the SQL queries, but, honestly, I can't see why they should. And I guess millions of web developers do like I do - skip the relationship thing altogether.
Please enlighten me, someone! (Or let's have an interesting discussion on this topic - if there is something to discuss, that is!)
Pettrer
June 5, 2006 at 4:24 pm
The relationships themselves do not speed up queries.
Primary and foreign keys provide great starting points for indexes which do speed up queries (if you build them correctly).
These relationships are also good for enforcing the data integrity of your database.
This is especially important if your database is being accessed by more than one application.
Hopefully I am understanding you post correctly in that you still break down the data in various tables, but you don't necessarily enforce referential integrity. Or perhaps you are not using foreign keys because you are storing data redundantly?
It is quite possible that the environment you work in does not lend itself to showing you the benefits good database design. Certainlywhen you start dealing with large volumes of data, good database design is essential.
I hope this explains some of the things you are interested in.
June 9, 2006 at 8:49 am
Steve,
Thanks for your comments. As I suspected, it all boils down to what your environment's size and complexity is. Still, it looks to me as if the whole point of using relationships is to not letting errors slip in. In other words, relationships are used to prevent bad database design, rather than to themselves create good database design, or so I think.
I do understand your remarks about beer though. 😉
Pettrer
June 9, 2006 at 9:56 am
Relationships do enhance the design process. By thinking them through, I wind up with a better DB design.
Established relationships (FK's especially) also help those that come after you. Try attempting to figure out the relationships between 30+ tables, most with the same / similar column names. There are relationships, but all are enforced within the code rather than the DB. (What I'm struggling with right now...)
June 10, 2006 at 4:21 pm
😉
Thanks for your input Pam!
P
June 11, 2006 at 9:23 pm
Pams commments are very valid.
They also point to the fact that if you maintain the referntial integrity in the database, some developer who comes along cannot destroy your data becuae they don't understand the relationships.
And by forcing you to think through the design of your database you can also ensure better performance.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply