database design for outsystem, whether to keeps master codes in transaction

Back to Forums

Linguo You

Discussion

SQL

Data

Database

How-to

for a large project, many transaction table columns is linked to master tables by foreign key, in outsystem, master table has identifier and unique codes for the master records

how to choose the foreign key
example:
master empolyee table:
columns: id, employee code, employee name
transaction table for employee activities:
columns: actvities, employee code, employee identifier.

Option 1. change identifier type to text
probably the best option, but seems like it has pertential issues in the long run. one i have encounter is somehow in muti-tenant systems, identifier has to be integer. I am not sure whether there are other Constraint.
so i decided to key the default integer type, which may save a lot of problem in future and most of the things are already handled by system like unqiueness.

Option 2, use default integer type identifier, i have to create additional column in master table to keep the code.
Question came: whether to keep the master code in transaction also?
if capture the master code in transaction table, it will be "unnessisery" as the code is already in master tables, and can be retrived by join the foreign key.

if donot capture the master code in transaction table, from reporting point of view, everyting i run a report, which normal will be based on the master codes, i need to join all required master tables to get the code

need expert's advise how to decide?
1. should i take option 1?
2 if choose option 2, should i keep the master codes in transaction?

22 Aug 2015

MVP

no doubt option 2.

as far as to keep the mastercode in the transaction as well.
it matters what the actual need is for the transaction-table, is it plainly for auditing/logging?
will the transaction-table be transferred to archives, another database?
the join should not be a problem.

it really depends on the usecase, number of records etc.

24 Aug 2015

1 reply

24 Aug 2015

Show thread

Hide thread

Linguo You

thanks Mr J for ur responds, the transaction tables is for daily transactions, for ERP scenario, it will be the table keeps the sales order, purchase order.
there will be audit table for the transactions, same transaction records will be logged in audit tables multiple times.

there will reports scanning both transaction and audit tables.
if the number of records cross 10k, each record need to join with 10~15 masters, will there be a performance issue?

J. wrote:

24 Aug 2015

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines