How to identify a transaction table?

Question

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

tr_reciept

i dont understand why these tables are tr tables?

Answer 1

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.