October 10, 2002 at 3:10 pm
Hi folks,
Hope you guys can make it clear for me. This is about database design. I am DBA but no programming skills. One programmer came up with 2 new tables. Let say one table is called Transaction and the other called Transaction_Input. The table Transcation has a PK on column T_ID. This table is to record Client_ID, Seller_ID,etc. when the transactions take place. The other table will have a column named T_ID as a FK to T_ID in table Transaction. This programmer created an ID column (identity 1,1) for table Transaction_Input and make it PK. In my opinion this isn't right to make ID as PK since it isn't helping with the clustered index. We aren't using this ID column to any query. All queries made to Transaction_Input will use "where T_ID = ". I wanted to make T_ID a primary key for Transcation_Input but the programmer disagreed, insisting 4 years in University make it clear that nothing is wrong with the existing design! I have no 4 year degree but I am sure that isn't right. The problem is the boss knows little about programming and has to rely on the programer to write the codes! What do you think?
Thx.
October 10, 2002 at 3:52 pm
First off Primary Key is defined as a column with unique values. If T_ID will have repeating values SQL will not allow you to use it, at least not by itself or you will break the uniqueness constraint. Also, a Primary Key does not have be be a clustered index, it can be non-clustred and any index can be clustered instead which if the primary key column is not going to be queried often it may be better to cluster on an index that contains a column that will.
There is some things you will have to think about in regards to clustering a non-Identity column and that is how to avoid page splits best. The the records may insert into the begining or middle of the table then you have to have a fill factor other than 0 or 100 or you will suffer from page splits often. If all the records will insert into the end or right near the end it will be same to use 0 or 95%+ fill factor (note 0 about the same as 100%).
Now if T_ID as the foreign key will not have more than row associated then why waste the space with another column, use T_ID and forget about the other or just make Transaction and Transaction_Input one table (note: you didn't say it would have multiple input lines so I throw that in with that in mind for you to consider).
Finally, if the Transaction_Input table has two columns that would make a compound unique key with T-ID included then, contrary to some folks beliefe, it will make a better PK and clustered index, there is no need for the extra identity column. And to add to the possiblity of one if these transactionas are added one at the time you should be able to add a transaction sequence columnt and control that keep up with that in the application, does require a bit of extra planning and programming but that is what I would do. Then I would make T_ID, T_SEQ a compound primary key with associated clustered index. This would make transactional reporting a bit easier on serveral reporting possiblities.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 10, 2002 at 4:24 pm
"First off Primary Key is defined as a column with unique values. If T_ID will have repeating values SQL will not allow you to use it, at least not by itself or you will break the uniqueness constraint. Also, a Primary Key does not have be be a clustered index, it can be non-clustred and any index can be clustered instead which if the primary key column is not going to be queried often it may be better to cluster on an index that contains a column that will."
T_ID will be unique in both tables. One row will be added to each table when the transaction occurs. The value of T_ID will inscrease one by one (identity in table Transaction), which eventually makes the new recoreds (even with or without column ID in table Transaction_ID) always the last row in two tables. The programmer just followed a rule that a table should have a PK; that was why column ID was added along with the clusted index for the PK. My point is why we don't use column T_ID due to its uniqueness and sequence. The rule for PK that I mentioned above didn't mean we must add the ID column to make it PK.
Due to the business needes, we can't make make Transaction and Transaction_Input into one table.
Thx.
October 10, 2002 at 4:33 pm
Then you are correct there is no absolutly need to add the extra column for another identity value if you will only have the one row for T_ID in the input table. And a PK can also be an FK from another table, so thus T_ID can be the PK in the Transaction_input table no need for another.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 10/10/2002 4:34:54 PM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply