CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008sql-server-2008-r2
Phill Greggan
Phill Greggan

Reputation: 2394

How to identify a transaction table?

i have database that has master tables prefixed with mt_ and transactions tables prefixed with tr_ . But when i go through the database i started to wonder what is the actual definition of a transaction table and master table. To my understanding transaction table should have a composite primary key (primary key made from two or many PKs of other tables). But when i looked at the transaction tables in database, there are tables that have the composite key as mentioned previously also it has tables tagged as tr_ but have only one key tagged as PK and they also have PK keys that belongs to other tables but they weren't even tagged as FK...

So could any one here explain the difference between a master table and a transaction table and how to identify them in DB?

Updated Here is an examtple of my db

tr_orders

OrderId int PK
CustomerId int Fk
OrderDate datetime  etc

tr_reciept

RecieptId int PK
OrderId int **(but not FK)**
PaindAmount money
recieptDate datetime

Here are the table structure of the complete two tables:

tr_orders

enter image description here

tr_reciept

enter image description here

i dont understand why these tables are tr tables?

Upvotes: 5

Views: 9615

Answers (2)

Jo Kemp
Jo Kemp

Reputation: 221

Master - - - - - - - - - - - - - - - - - - - - - - Transaction

Country .... Employeee ...... Customer ........... Order

Master & Lookup tables exist in a Range, not a Binary On/Off State, and reflects the expectation of the amount of activity the table will experience

Lookup/Reference tables like State, Currency, Country, etc. RARELY have new records or changes -- Very "Master"

Employees add or change records occasionally, but not often (hopefully) so more "Master" than "Transaction"

Customers add or change records more often than Employees (also hopefully) so still a measure of "Master" but also with "Transaction" qualities

Orders are added and changed ALL the time (hopefully) and are Very "Transaction," Same with Reciepts

If a table has No FK fields, it's likely very "Master"

Tables with FKs have some amount of "Transaction" to them, the more you expect new records - the more "Transaction" the table could be.

Keys:

in my opinion, every table should have a surrogate PK, not related to FKs or any Natural keys. Lots of reasons for this opinion, but whatever works for you is cool, too.

Often, if there is an obvious Natural key, then a table needs a Unique constraint for that key, in addition to the PK

Upvotes: 1

Nick.Mc
Nick.Mc

Reputation: 19204

Why you don't always put a Primary Key on all the Foreign Keys

When something 'happens' it goes into a transaction. Someone buys a toy at a shop. A row is created recording that it was a toy and the datetime it happened and how much it cost.

The someone else buys a toy ten minutes later

We have two records in our transaction table:

Date          Time     Product_Key     Shop_Key    Amount
--------------------------------------------------------------------------
18 Dec 2015   13:05    7                12           10
18 Dec 2015   13:15    7                12           10

Here we have two foreign keys: Product_Key and Shop_Key

We can't create a PK on just those two foreign keys because then one shop could only ever sell one toy.

So the PK does not automatically go on all the FK's

But really the thing to take away is that your data model (tables, fields, keys, datatypes) reflects what your business does. If a shop could truly only ever sell one toy, it would be a valid data model to have a PK on those two fields.

Some characteristics of 'transactional vs master tables

"Transactional" and "master" tables generally have a many to one relationship, meaning many transactions match one master record. Many purchase records match the same single toy record. A FK is a dead giveaway to this kind of relationship although "master" tables also have FK's

"Transactional" tables usually have a date or some kind of event id and are often 'aggregated' when reporting. This could be a record count or a sum of an amount.

Some characteristics of real world systems

It's entirely likely that someone forgot to put on a FK or PK, or it could be that there is a unique key (not a PK) enforcing what you are expectig to see.

I've seen live systems where the keys were clearly incorrect, or there were no keys at all.

Upvotes: 4

Related Questions