June 1, 2009 at 2:47 pm
I heard normalization and over normalization a lot. But I don't know to what level it is an over normalization.
We have a database that has lots of performance issues. I want to get your opinion on if it is over normalization.
We have a table that logs customer action every so often for years. Instead of using ActionTime, we use ActionTimeID in Action table. The ActionTime is stored in a different table called ActionTime. ActionTime table has only two columns -- ActionTime and ActionTimeID. So each time we need to get customer action info we need to join the two big tables.
Is there any benefit to use the separate Time table? I guess the idea of using ActionTimeID is to use a smaller datatype to save space (we do have thousands of customers). But I think the overhead of a new table might overweight the space gain. Not to maintion lots of extra work to add and get the infomation in and from the database.
Is there any benefit of using an int data vs a datetime data?
June 1, 2009 at 3:00 pm
I can't fully judge it based just on your description, but I'm going to say that's not normalization, that's something else. It's based on a relatively common misconception of what is meant by "normalization", which is also usually accompanied by the mistaken idea that "relational database" means it's a database where the tables are related to each other by foreign keys (also incorrect).
Most likely, that structure is causing performance issues.
- 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 1, 2009 at 3:24 pm
Hi:
This is a common practice in datawharehousing. That smaller key is named subrogated key. You can save space, yes. But I think that's not the main purpose of it. Normally, in the second table where you match the subrogated key with the real key (the date), you have more than two fields. For example, you can have de date, the month in numbers (1, 2, 3, 4), the month in text (Jan, Feb, Mar...), the quarter, the semester, the year, and so on. The advantage is to have the possibility to aggregate and analyze the data not only per date basis but by all the fields in the second table (daily, weekly, monthly, etc.).
There you have an advantage: one subrogated key in the main table, many keys in the second table.
In the current scenario, I don't find any benefit of having that second table.
I hope this helped you
Alberto
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
June 2, 2009 at 8:49 am
Thank you.
This structure is causing issues. We have several two column (ID and datetime) tables in the database. Join is used a lot.
The database is in production and has years of data and is still recording data. Is there a way to change schema or must live with it?
June 2, 2009 at 9:30 am
have you have a look at the index fragmentation? as this could cause issues over time as data is entered
June 2, 2009 at 9:35 am
hshen (6/2/2009)
Thank you.This structure is causing issues. We have several two column (ID and datetime) tables in the database. Join is used a lot.
The database is in production and has years of data and is still recording data. Is there a way to change schema or must live with it?
There are ways to rebuild that, but it takes a bit of work in most cases. Start by analyzing the structure as it is, with a lot of attention to details, then work out what you think the structure should be, then start testing modifications.
- 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply