June 10, 2008 at 8:11 am
hi,
:unsure: i've just started with a smallish company and have found every table is pked/ cluster indexed with a field called id, in the foreign table the id is referred to as TablenameID so fk_itemid = (item.id = itemcontent.itemid). The question is .. is this bad practice or am i behind the times with OOD databases.
thanks
Mark
June 10, 2008 at 8:25 am
It sounds about right
Table1(
pkid
fkTable2ID
fkTable3ID
)
or simular to that. I've seen tables with table1.id table1.schoolid table1.number table1.entityid
i mean, wtf... really. Turned out that id was the true id, but schoolid could stay the same with different ids
eg..
id,schoolid,number,entityid
1,2001,181,2001
2,2000,150,2000
3,2001,180,2001
Turned out that the schoolid and entity id where always the same, and some other table pointed to the 'current' id number for a schoolid. So a whole seperate table for something like table2(schoolid, currentid) where currentid pointed to id in table1.
Number was like a guid/uuid of the sort. This was from a 'professional' SIS(Student Information System)
So you have a leg-up on the fact that you're even worried about this.
I've also seen SIS dbs where a table was only a composite of foreign keys
eg, table3(fk_class, fk_teacher, fk_school)
AND there were DUPLICATES in the table, all over. Like 1/2 of the table was dupliate entries. All it was were 3 fk's and it had duplcate entries.
June 10, 2008 at 9:11 am
Thanks bcronce,
its not quite as dire as what you found, the db looks about 4th normal with composite key tables joining where necessary. I can see why the dev had done it because hes persisting down to .net 2.0 objects based on the tables using managed code sprocs so user.id makes more sense than user.userid in that context.
Just feels wrong call an ident column 'id'.
thanks for the reply, i'm sure i'll probably leave it and have a nightmare with index management later.
Mark
June 10, 2008 at 9:28 am
no one was saying anything, so I replied 😛
Somewhere on this site is a nice Article on 'Must have Rules' for DBs, which includes pk/fk naming standards.
I'm sure someone else will give you a heads up, this is a good community.
If someone can point him to the article. Otherwise, just go look around, most are really good.
<--- I'm not the most helpful on this 🙁
June 10, 2008 at 11:58 am
I've seen that naming convention before. The DBA who established it as standard at the company he worked for pointed out that having the table name in the ID is kind of overkill, because it's going to end up with the table and a period in front of it most of the time anyway.
I don't use that standard, I find it easier to read the code where the ID has the table name in it, but there's nothing technically wrong with it. It's just a style thing. So long as it's consistent, it works just fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 10, 2008 at 12:11 pm
I would agree. The standard is not what I would normally do, but as long as it is consistent, there is nothing really wrong with it.
Just be happy there is a standard at all.
June 10, 2008 at 12:15 pm
Piling on.
I've seen that standard before. I don't care for it, but as long as it's applied consistently, it's readable and it won't hurt anything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2008 at 1:54 am
Thanks everyone for your responses, nice to know its not going to break anything further down the line (replication, indexed views etc).Would have meant quite a bit of pain changing it .... now i have to live with it.
Thanks again
Mark
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply