Putting indices on tables - PK's, FK's and all the rest?
Date: Mon, 14 Apr 2003 02:47:59 +0100
Message-ID: <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
(
Title VARCHAR(5) 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
);
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 - 03:47:59 CEST