| Subject | Re: Foriegn keys as Composite Primary Key |
|---|---|
| Author | Adam |
| Post date | 2005-07-17T06:03:04Z |
Hi Vishal,
instead of posting a new message. Everyone had then responded to their
question and yours quickly fell up the list. Because it had the same
subject, it looked like your question was answered when really most
people probably missed it. It's just forum ettiquette. If your post
doesn't answer the question asked, or at the very least have something
to do with the question being asked (such as request further info
etc), then it should be asked as a new post. It just gives everyone a
fair chance at being answered.
* Use foreign keys and triggers to enforce consistency, don't leave it
to your application developers to do the right thing, because
pressured by deadlines etc they WILL forget and then you would need to
deal with inconsistent data. It is so much easier to prevent it from
being inserted in the first place.
* If a NULL value makes no sense, declare the field as not null
* Transactions should not be left open for long periods of time unless
there is a good reason. It prevents garbage collection for records
that may still be of interest.
* Any index that has low selectivity can be combined with the primary
key of the table to improve its usefulness.
* Know that certain operations will be inherently slower in MGA
databases, such as select count(*) operations.
* Make use of the MGA features, if you think you need to declare locks
on tables then there is probably a more efficient way of achieving
your desired result.
* Know the difference between Superserver, Classic and Embedded and
use the right one for your platform and requirements.
* Use generators to make your PKs rather than something like a
timestamp where uniqueness may not be guaranteed.
* Leave forced writes on if you care about your DB.
* Check every query that goes into your application. There are some
cases where the optimiser tries to do things the wrong way which
results in slow performance. Learn how to read the PLAN (then teach me
:) ), or use a tool to check the PLAN on a realistic test database.
* If you are using the same query repetitively, use it parameterised,
that way it only needs to be prepared once which will really save some
time.
Adam
> PS:Unfortunately someone hijacked your thread, butBasically someone had replied to your question to ask a question
> I saw your question...what yu mean by this statement?
>
instead of posting a new message. Everyone had then responded to their
question and yours quickly fell up the list. Because it had the same
subject, it looked like your question was answered when really most
people probably missed it. It's just forum ettiquette. If your post
doesn't answer the question asked, or at the very least have something
to do with the question being asked (such as request further info
etc), then it should be asked as a new post. It just gives everyone a
fair chance at being answered.
> could you please also give some more of your good practices for* Normalise your database
> Database design,and Firebird related..
* Use foreign keys and triggers to enforce consistency, don't leave it
to your application developers to do the right thing, because
pressured by deadlines etc they WILL forget and then you would need to
deal with inconsistent data. It is so much easier to prevent it from
being inserted in the first place.
* If a NULL value makes no sense, declare the field as not null
* Transactions should not be left open for long periods of time unless
there is a good reason. It prevents garbage collection for records
that may still be of interest.
* Any index that has low selectivity can be combined with the primary
key of the table to improve its usefulness.
* Know that certain operations will be inherently slower in MGA
databases, such as select count(*) operations.
* Make use of the MGA features, if you think you need to declare locks
on tables then there is probably a more efficient way of achieving
your desired result.
* Know the difference between Superserver, Classic and Embedded and
use the right one for your platform and requirements.
* Use generators to make your PKs rather than something like a
timestamp where uniqueness may not be guaranteed.
* Leave forced writes on if you care about your DB.
* Check every query that goes into your application. There are some
cases where the optimiser tries to do things the wrong way which
results in slow performance. Learn how to read the PLAN (then teach me
:) ), or use a tool to check the PLAN on a realistic test database.
* If you are using the same query repetitively, use it parameterised,
that way it only needs to be prepared once which will really save some
time.
Adam