I have normalized my database, but I still have one table that I am not sure about. It has a primary key that is related to my main object. I have one column that stores a date and the other 9 columns store codes from another table and the codes are only 3-5 characters. So my question is should I remove the codes and store the code id's instead. I know the easy answer is yes, but since the codes are so small is it going to make any difference? The sql statements are easier to write to when I store the codes instead of the id's.
3-5 character codes are fine, as long as they are primary/unique keys to the parent table and you declare a foreign key for each one. However, if you think the codes will often change (e.g. code for "Boomerang" may change from "BOOM" to "BMRG" for some reason) and you want to avoid cascading updates, you could use surrogate keys ("ID"s) instead.
The nine colums will always be the same. There is a colum for each day of the week and 2 for other things. Each row will have a id number, a code for each day of the week and the 2 other ones, and a date. r937 can you see anything wrong with this? actually I just saw something, I will need the Id number of the parent object so that I will be able to store multiple dates for each parent object. Anything else?
not trying to be argumentative, but you can physically put this into any database:
create table items
( id integer
, categories varchar(200)
insert into items values ( 1, '12,14,16,18,21' )
insert into items values ( 2, '9,10,56' )
insert into items values ( 3, '11,34,67,937' )
but i would not say it was 1NF
i see this type of structure all the time by programmers making the (obviously difficult) transition to databases
tell them to "normalize" by creating a relationship table with a single column for the category id, and half of them complain that they would rather use those neato string handling functions in their programming language and "join" to the categories table by querying one category at a time in a loop
I agree with you, but this is where it all gets a bit fuzzy. From a purist point of view, this is in 1NF, because each categories column contains a single value, of domain character string (and the fact that it contains commas is irrelevant - the database is ignorant of them). But of course, it goes against the spirit of 1NF and is in fact being used by the programmer to "work around" the pesky 1NF restriction, and is precisely as reprehensible as building a true 0NF table, if it were possible to do so...