Re: Putting indices on tables - PK's, FK's and all the rest?

If the tables are with some cardinality, it is no need to create indices manually. By default, some database engine will create indices for some special columns, like PK or FK.

What's more, you can always try it since your database is within a small scale.

regards,
Qingqing

"Paul Linehan" <paul_at_not.a.chance.ie> wrote in message news:4k4k9v8e3bbfeviso7545nnv38aju7tfrn_at_4ax.com...
>
>
> Hi all,
>
>
> I have a number of small lookup tables that I use in an application
> that I'm currently developing - see scripts at bottom of post. It is
> possible that *_some_* records may be added to these tables, but
> whatever happens, they will never be more than about twice their
> current size (I've also included the data in the scripts).
>
>
> I'm wondering whether for such very small tables, I need to (or
> should) create PK indices (as Integers - i.e. TableName_ID?) on these
> tables for use as foreign keys in other tables, or whether I should do
> the Referential Integrity (R.I.) stuff in triggers and code?
>
>
> Given the tiny size of these tables, is it worth bothering with PKs
> for such small tables? I was of the understanding that if one makes
> these tables foreign keys, an index is created regardless of what I
> do?
>
>
> I am worried about these indices becoming skewed.
>
>
> I will have the db like
>
>
> Name: 'Paul Linehan', Title: 1, ....
>
> it will be
>
> Name: 'Paul Linehan', Title: 'Mr.'...
>
>
> Is there a threshold/rule-of-thumb for where it's good to start
> putting in PKs and/or declaring stuff as a foreign key (in terms of
> recordnumbers in the table and/or record size)?
>
>
> I have one table with 50 (and potentially more) records that I am
> planning to declare as an FK.
>
>
>
> Thanks for any input &c.
>
>
>
> Paul...
>
>
> --
>
> plinehan__AT__yahoo__DOT__com
>
> C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04
>
>
> -------------------------
> /* Table OigeCategory */
>
> CREATE TABLE OigeCategory
> (
> CategoryName VARCHAR(8) NOT NULL
> );
>
> INSERT INTO OigeCategory Values('Senior');
> INSERT INTO OigeCategory Values('Junior');
> INSERT INTO OigeCategory Values('Leader');
> INSERT INTO OigeCategory Values('Life');
> INSERT INTO OigeCategory Values('Staff');
>
> COMMIT;
> -----------------------
>
> /* Table Salutation */
>
> CREATE TABLE Salutation
>

> Title VARCHAR(5) NOT NULL,
> );
>
> INSERT INTO Salutation VALUES('Mr.');
> INSERT INTO Salutation VALUES('Ms.');
> INSERT INTO Salutation VALUES('Mrs.');
> INSERT INTO Salutation VALUES('Dr.');
> INSERT INTO Salutation VALUES('Prof.');
> INSERT INTO Salutation VALUES('Capt.');
> INSERT INTO Salutation VALUES('Col.');
> INSERT INTO Salutation VALUES('Gen.');
>
> COMMIT;
> ------------------------------
>
> // for example, you could add 'Mon.' (Monsenior), 'Rev.' (Reverend)
> and 'Fr.' (Father) here, but it's unlikely that the table will ever go
> beyond 10 odd records.
>
> ----------------------
> Create TABLE OigePayment
> (
> PaymentType VARCHAR(12)
> );
>
>
> INSERT INTO OigePayment Values('Cash');
> INSERT INTO OigePayment Values('Cheque');
> INSERT INTO OigePayment Values('Draft');
> INSERT INTO OigePayment Values('Direct Debit');
> INSERT INTO OigePayment Values('Agent');
> INSERT INTO OigePayment Values('Laser');
> INSERT INTO OigePayment Values('Credit Card');
>
> COMMIT;
> ----------------------
>
>
>
> plinehan__AT__yahoo__DOT__com
>
> C++ Builder 5, SP 1. IB 6.0.1.6 IBX 5.04
Received on Mon Apr 14 2003 - 05:16:47 CEST