Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm creating a parameter table in our database. This table will have 1 row with values that are environment specific (production, development, etc).

Is there any reason why I should define an index on this table?

Update
This table is a parameter table which will be used to drive our job queue system. The table will be defined like this:

QueuePrm
  LogLvl    integer
  ShowMs    boolean
  Restart   boolean

This table will only ever have 1 row. No other table in our system will reference it.

share|improve this question
1  
Double checking - do you mean one Row or one Column? –  GrandmasterB Jun 4 '12 at 17:49
1  
Are you unable to create the table without one or is there some other reason to ask? –  JeffO Jun 4 '12 at 18:40
1  
The real question is why don't you have a key-value-pair table instead? Each of your fields could be represented as an integer or a string in the Value column. –  pdr Jun 4 '12 at 19:04
2  
@pdr I'm not a fan of the inner-platform anti-pattern. –  briddums Jun 4 '12 at 19:20
1  
@briddums: I don't see the connection. It's not about making your application infinitely extensible, although there's no question that adding a row will be easier than adding a column, as your application expands. The reason for using key-values is that it's much quicker to access a single value. –  pdr Jun 4 '12 at 19:31

4 Answers 4

up vote 7 down vote accepted

No, you do not need an index for a one-row table, nor do you need a primary key. The only purpose of an index is to speed certain operations by making it possible to locate a row without reading the entire table.

Many relational database implementations require indexes for foreign-key or uniqueness constraints (including primary keys). That is not a logical necessity. It is done because an index will be desired in almost all cases, and it isn't worth the trouble to support these constraints without an index. When a table is so small that an index isn't useful, the index doesn't cost much.

share|improve this answer
1  
Some RDBMS will create their own in the background anyway. –  JeffO Jun 4 '12 at 18:39
1  
+1. With a single row table, he shouldnt need to access it in queries via joins anyways. The values in the table could be loaded once when the page is loaded or when the client starts, and the values then used in queries directly. –  GrandmasterB Jun 4 '12 at 18:44
    
@GrandmasterB: Exactly. –  kevin cline Jun 4 '12 at 19:13

While it looks like there is no need now, tables like this become filled with values as the system progressively ages. You might insist on multiple tables for keeping different parameters segregated, but eventually all tables like this expand.

It is best to start with a key, descriptive of the purpose if possible. Future maintenance staff appreciate understandable names.

share|improve this answer

At the very least, the primary key of the table (you DO have a PK identifier, yes?) should have an index, also created as the clustered index by default in MS SQL Server.

If you have any secondary keys (such as a 'name' identifier column) that will be a selection criterion, index it as well.

share|improve this answer
    
Why would I add a primary key on a table that will only have one row? –  briddums Jun 4 '12 at 17:47
1  
For future expansion - there could very well be a situation where another table needs a foreign key reference to that row, or maybe a second or third row becomes added for additional configuration scenarios, or you want to delete the existing row and add a new one you then have an identifier to associate with it. –  Jesse C. Slicer Jun 4 '12 at 17:52
1  
Would it really be that hard to add another column, index, PK to a table that isn't referenced by any other table until you need it? –  JeffO Jun 4 '12 at 18:38

Instead of making a one-row table with many columns, wouldn't it be neater to design it with many rows instead? Like

ParamName | Value
------------------
LogLevel  | 5
BuildNr   | 3.0.9
ErrorMail | error@example.tld

Here ParamName will be the Primary Key.

This has the added effect that when you need to add more parameters, you can just INSERT them without having to update your tables and possibly your SQL queries.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.