Tricky SQL statement over 3 tables

Question

I have 3 different transaction tables, which look very similar, but have slight differences. This comes from the fact that there are 3 different transaction types; depending on the transaction types the columns change, so to get them in 3NF I need to have them in separate tables (right?).

As an example:
t1: date,user,amount

t2: date,user,who,amount

t3: date,user,what,amount

Now I need a query who is going to get me all transactions in each table for the same user, something like

select * from t1,t2,t3 where user='me'; (which of course doesn't work).

I am studying JOIN statements but haven't got around the right way to do this. Thanks.

EDIT: Actually I need then all of the columns from every table, not just the ones who are the same.

---

EDIT #2: Yeah,having transaction_type doesn't break 3NF, of course - so maybe my design is utterly wrong. Here is what really happens (it's an alternative currency system):
- Transactions are between users, like mutual credit. So units get swapped between users.
- Inventarizations are physical stuff brought into the system; a user gets units for this.
- Consumations are physical stuff consumed; a user has to pay units for this.

|--------------------------------------------------------------------------|
|  type     |  transactions       |  inventarizations  |  consumations     |
|--------------------------------------------------------------------------|
|  columns  |  date               |  date              |  date             |
|           |  creditor(FK user)  |  creditor(FK user) |                   |
|           |  debitor(FK user)   |                    |  debitor(FK user) |
|           |  service(FK service)|                    |                   |
|           |                     |  asset(FK asset)   |  asset(FK asset)  |
|           |  amount             |  amount            |  amount           |
|           |                     |                    |  price            |
|--------------------------------------------------------------------------|

(Note that 'amount' is in different units;these are the entries and calculations are made on those amounts. Outside the scope to explain why, but these are the fields).
So the question changes to "Can/should this be in one table or be multiple tables (as I have it for now)?" I need the previously described SQL statement to display running balances.

(Should this now become a new question altogether or is that OK to EDIT?).

EDIT #3: As EDIT #2 actually transforms this to a new question, I also decided to post a new question. (I hope this is ok?).

Answer 1

You should consider STI "architecture" (single table inheritance). I.e. put all different columns into one table, and put them all under one index.

In addition you may want to add indexes to other columns you're making selection.

Answer 2

What is the result schema going to look like? - If you only want the minimal columns that are in all 3 tables, then it's easy, you would just UNION the results:

SELECT Date, User, Amount from t1 where user = 'me'
UNION
SELECT Date, User, Amount from t2 where user = 'me'
UNION
SELECT Date, User, Amount from t3 where user = 'me'

Answer 3

Or you could 'SubClass' them

  Create Table Transaction
  ( 
     TransactionId Integer Primary Key Not Null,
     TransactionDateTime dateTime Not Null,
     TransactionType Integer Not Null, 
     -- Othe columns all transactions Share
  )

  Create Table Type1Transactions
  {
     TransactionId Integer PrimaryKey Not Null,
     // Type 1 specific columns
  }
  ALTER TABLE Type1Transactions  WITH CHECK ADD  CONSTRAINT 
   [FK_Type1Transaction_Transaction] FOREIGN KEY([TransactionId])
    REFERENCES [Transaction] ([TransactionId])

Repeat for other types of transactions...

Answer 4

You can supply defaults as constants in the select statements for columns where you have no data;

so

SELECT Date, User, Amount, 'NotApplicable' as Who, 'NotApplicable' as What from t1 where user = 'me'
UNION
SELECT Date, User, Amount, Who, 'NotApplicable' from t2 where user = 'me'
UNION
SELECT Date, User, Amount, 'NotApplicable', What from t3 where user = 'me'

which assumes that Who And What are string type columns. You could use Null as well, but some kind of placeholder is needed.

I think that placing your additional information in a separate table and keeping all transactions in a single table will work better for you though, unless there is some other detail I've missed.

Answer 5

I think the meat of your question is here:

depending on the transaction types the columns change, so to get them in 3NF I need to have them in separate tables (right?).

I'm no 3NF expert, but I would approach your schema a little differently (which might clear up your SQL a bit).

It looks like your data elements are as such: date, user, amount, who, and what. With that in mind, a more normalized schema might look something like this:

User
----
id, user info (username, etc)

Who
---
id, who info

What
----
id, what info

Transaction
-----------
id, date, amount, user_id, who_id, what_id

Your foreign key constraint verbiage will vary based on database implementation, but this is a little clearer (and extendable).

Answer 6

What about simply leaving the unnecessary columns null and adding a TransactionType column? This would result in a simple SELECT statement.

Answer 7

select *
from (
    select user from t1
    union
    select user from t2
    union
    select user from t3
) u
left outer join t1 on u.user=t1.user
left outer join t2 on u.user=t2.user
left outer join t3 on u.user=t3.user