Re: Should I use database foreign keys and indexes

I've been working in web for around 2 years now, although I've worked in the database arena for about 4 years. I've also worked with web developers who chose prefer heavily denormalized schemas with no indexes or relational integrety.

There's 2 bad things I've noticed from this approach. Firstly the clients data can get out of shape very quickly. Without any rules in place it's up to the web-pages to ensure they execute SQL that leaves the data in a 'valid' state. Even if the SQL does something that breaks the business rules, the database won't grumble because there's nothing telling it about that business rule. If you think about it, a database's purpose in life is to store data and keep it in a fit state. Using it as a simple 'data-bin' is neglecting is key strengths.

Another problem with this approach is that, whilst the denormalised structure may be ok for todays needs, it won't adapt well to tommorrows. Cleints like to lever the benefits of the instant-deployment that comes with web-development, allowing them to have new features on a weekly/monthly basis if they like. What happens is that the flat-files just get bigger, and the application code gets messier. I've seen web-apps that are nearly beyond maintenance within 12 months of creation, and thats largely because they work to crappy database designs.

Another thought is that, IMHO, a normalised database requires a more sophisticated application. Writing web forms to work with m-m relationships is more difficult that writing something to insert a comma-delimited list into a table cell, for example. So, the all-in-one developer is making his life easier through denormalisation. This may be becuase he's planning his database based on his web-forms.

One handy debating tool in database deisign is views. Views can simplify the most complex of schemas. Therefore, you could persuade your team-mates that getting at the data isn't going to be a slow and painful development process, becuase you'll make some nice views to present data as it is required.

I realise you're probably aware of these issues already, but I just wanted to state how I see it!

HTH Tobin Harris

"Carl" <carl.stottlemyer_at_lmco.com> wrote in message news:bc814416.0207101313.442eea0f_at_posting.google.com...
> I recently had a discussion with several web application developers
> where I work about the use of foreign keys and table indexes.
>
> I always thought that they were both basic elements of relational
> database modeling and should be included in all of your database
> designs. I was suprised that they felt that foreign keys and indexes
> add unnecessary complexity to a database and that they never use
> foreign key constraints and rarely use indexes as a primary key. I
> would like to know your thoughts on using foreign keys and indexes in
> a database. The pros and cons I have thought of thus far are:
>
> Foreign Keys Constraints:
> Pro:
> Provide referential integrity
> Con:
> May have to disable when you are doing mass updates
>
> Table Indexes as Primary Key:
> Pro:
> Unique reference to the table so that a change to a datafield
> doesn't require a change to all tables that reference it
> Con:
> May have to do a table join to search on a particular table field
>
> Please add any comments you have.
>
> Thanks,
> Carl
Received on Thu Jul 11 2002 - 10:54:27 CEST