June 16, 2011 at 6:30 pm
Hi All,
I have a transaction table joining that with a master table always many searches and reports are generated.
transaction table is kind of order table and master table is nothing but customer table.
the transaction table can be archived once in 3 months.
the transaction table needs to be joined with multiple master table to produce the desired result.
As the reports, boards and searches query the trans table and master tables always, can we have the important master table information stored along with the trans table? so that the application will be using only the trans table for searches and queries?
the time spent on joining the huge master table will be saved. But this is OLTP only and not OLAP.
Is this a right approach?
Thanks,
Regards,
Ami.
June 16, 2011 at 7:06 pm
You'd have to give more details, but likely the master table isn't going to slow you down if it's not very large. It will probably live in memory and be joined quickly. SQL Server is very efficient at this.
If you move the master information to the transaction table, then you are denormalizing the table, which can be OK, but if you need to change master information, you might end up making lots of updates to the transaction table.
If you are talking about changing the archive structure to include master information, that is more like a data warehouse, and you could, but again, not sure this is the best choice. Hard to give more advice without more details.
June 16, 2011 at 11:54 pm
Hi Steve,
Thanks for your reply.
To be more precise this is for a vehicle repairing company. actually, the want the master details in the reports without modification. If the customer is holding a vehicle last year and this year the same vehicle is owned by some other customer they need those information. as it is not a datawarehouse i'm unable to go for CDC.
if the vehicle repaired in last year with say 'cust1' and this year it owned by 'cust2' then in the reports i need for last year cust1 and this year cust2.
so when there is a change in master details no need to change trans info....
number of rows:
trans table : 963600
master tables:
400300
1963500
1200500
So can i go forward adding master info into trans??
Thanks,
Ami
June 17, 2011 at 6:53 am
Anamika (6/16/2011)
...vehicle repaired in last year with say 'cust1' and this year it owned by 'cust2' then in the reports i need for last year cust1 and this year cust2.
First post states transaction table is archived every three month - does it means data older than three month gets purged from online transaction table?
If that's the case, how it works with statement about "last year" and "this year"?
Either way, in general - since this is an OLTP system that serves some reporting - I would favor a normallized data modeling at least to 3NF where each "transaction" row points to the right "master" table customer, let's say "transaction" table says:
VIN=12345,Date=01/05/2011,CustomerID=111;
VIN=12345,Date=17/06/2011,CustomerID=222
while "master" table says:
CustomerID=111, Name=Pete;
CustomerID=222, Name=Mike.
Bottom line, most of the time the exercise of partiallly (or totally) denormilizing a OLTP database just to better serve a couple of reports ends up in ruining OLTP performance. If reporiting is a concern, I would build a separate, proper reporting structure.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 17, 2011 at 8:54 am
Thanks Paul,
It works in this way, say last year December and this year say February.
🙂
But I totally agree with you. Thats how I built the database. (Now, we are doing some design considerations) We maintained the customer information and vehicle information in individual tables. Reason behind is the same vehicle can be bought by another customer. that time we don't have to recreate the vehicle details and the service history remains with us.
So both the master tables are huge.
Now, we have a system which captures the appoitments to the vehicles. Which has many boards and all the boards reflects the customer information. Just for a vehicle number, model etc do I have to join a giant with my tiny transaction table?
Here the beauty is the master tables are giants and trans are tiny ( to get better performance I'm planning to archieve with in 3 months). Reports for more than 3 months needs to query the archived DB.(The archieval plan is in discussion.)
Paul said....
Either way, in general - since this is an OLTP system that serves some reporting - I would favor a normallized data modeling at least to 3NF where each "transaction" row points to the right "master" table customer, let's say "transaction" table says:
VIN=12345,Date=01/05/2011,CustomerID=111;
VIN=12345,Date=17/06/2011,CustomerID=222
while "master" table says:
CustomerID=111, Name=Pete;
CustomerID=222, Name=Mike.
the above example is very much true.
on 1st May 2011 the vehicle was owned by Pete and,
on 17th June 2011 the vehicle belongs to Mike.
the same I need from the transaction tables.
Thanks,
Regards,
Viji
June 17, 2011 at 9:28 am
Anamika (6/17/2011)
Here the beauty is the master tables are giants and trans are tiny
This is very good news, since driving table on those reports is the tiny "transactions" table I see no reason for and actual join against the giant "master" tables. I would use "master" tables as lookup tables meaning that "transaction" columns like VIN and CustomerID have to be FKs pointing to a single "master" row.
I think the key here is a proper indexing strategy.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 17, 2011 at 10:08 am
You need to mock up a little more DDL. It's still not clear what you're intending.
CDC isn't necessarily what you would use. CDC is for detecting changes, but in general over a short period of time. You wouldn't keep the CDC data for reporting over a long time as it's a lot of data. Instead you'd build a slowly changing dimension that includes the data that has changed.
Typically people handle this with multiple rows in the transaction table that show the history, but it's not clear what you are doing. Master v transaction table isn't clear and I think you are using the terms in a way that I'm not familiar with.
June 17, 2011 at 12:16 pm
Steve Jones - SSC Editor (6/17/2011)
Master v transaction table isn't clear and I think you are using the terms in a way that I'm not familiar with.
I think this is a variation of CSI's naming convention on the old-n-good TOTAL DMBS times.
Master Table
Table that stores a single row per "master key", like VIN on Vehicle table in this case
Transaction Table (SCI's "Variable Table")
Table that stores a variable number of rows per "master key", like VIN in VehicleRepairs table.
Basically a 1:N relationship between Master and Transaction/Variable
Please tell me if I got it wrong 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply