[RESOLVED] Sorting from multiple tables

[DigiRev]

I am helping a friend with his PHP/MySQL site and he has 2 tables.

Each table has a DATETIME field. I need to get all records from these tables, and display them in order of date.

I can't just display the table 1 by order, then table 2 underneath it. They need to be sorted and displayed together as if they came from the same table.

I have looked into SQL joins but I'm not sure if that's what I should be using. I also tried getting the results from table 1 (ORDER BY date), putting it into an array. Then results from table 2 and doing the same thing.

Then I merged the 2 arrays and tried to sort it like that, but it's not working, and I'm sure there's gotta be a better way?

[visualAd]

You can use a union query to join the two results together but I would be inclined to say that maybe the underlying data schema needs revising if you you need to join two result sets together. If you post the table structures we may be able to help you with that.

[penagate]

You could use a union to select the two tables into a temporary table and then select from that to perform your sort.

[DigiRev]

I agree that the tables might be able to be arranged better. I've never heard of a union before but I'm looking into that now. This is the table structure. One stores withdraws from an account, and another, deposits.

TABLE deposits and TABLE withdrawals
id (smallint) UNSIGNED auto_increment PRIMARY KEY
user_id UNSIGNED index
account_id UNSIGNED index
amount FLOAT
the_date DATETIME

If I had been the one that setup the tables, I might have done it different.

Should I just make one table? "Transactions". And use a negative value for amount for a withdrawal? And a positive value for deposit?

[penagate]

Should I just make one table? "Transactions". And use a negative value for amount for a withdrawal? And a positive value for deposit?

That's what I'd do.


Think of tables like classes: they represent a type.
A debit and a credit are both a transaction: the data is of a transaction type. It makes no sense to separate them into debit and credit tables; indeed, this makes even trivial aggregation something of a nightmare. Better to do as you suggest and simply use negative values for debits and positives for credits. After all, a debit is simply a negative credit, and vice versa.

Any time that you have to think very hard about the relationship between tables is an indication that your schema is wrong.

[DigiRev]

Thanks, penagate. That's what I did and it's working now. He's still learning so he wasn't really sure how to setup the tables the best way. I'm still learning too so thanks for confirming my idea.

[visualAd]

Normal form will help guide you here. Most of the time us developers do it naturally but it can be used to explain why you needed to merge the transactions together here.

There are six normal forms (seven if you count Boyce Cod); and each normal form helps you step back into the anatomy of the data. Below are the first three which is what the majority of developers aim for in systems.

• When a table adheres to first normal form, each and every entity should be separated into its own table known as a relation. It can also be seen as the removal of duplicate values. I.e: if transactions and accounts were in the same table the account information would be repeating therefore there is an obvious need to separate these out into tables. In addition it may also be necessary to add identifiers to ensure uniqueness (customer IDs for example)
  
• Second normal form is a relation in first normal form and in addition each attribute must be dependant on the whole primary key (prime attributes form primary keys this can be one or more attributes that uniquely identify the record). For example the employee department table may include the employee ID and the department ID. Both these pieces of information uniquely identify the record and thus any other attributes should be related in whole to them. A department join date and leave date for example. A violation of this however would be inclusion of a attribute containing the date the employee started with the bank as this attribute relates only to the employee ID. Again violations such as these are quite easy to spot when the schema is being designed.
  
• For a table to adhere to third normal form all attributes must be transitively dependant on the primary attributes. That is each attribute should rely solely on the primary key. A common example would be the customer table containing the address of an employee. It is perfectly fessable that a customer be living at more than one address at the same time and / or that the customer will move to a new address at a later date. The address details are therefore not transitively dependant on the customer ID and thus the table is not in third normal form.
  
• Finally you have Boyce Cod normal form which is what you should be aiming form. This is an extension of the third normal form. Lets take the accounts table for example. Suppose it has both the customer ID and the customers NI/Social Security number. Let us take a customer accounts table for example (this is in the event that a customer can hold multiple accounts and an account can have multiple customers - you see similar things in business accounts). The the table which links them together has the customer number, their national insurance number and the account number. In this case there are two possible primary keys (account number and customer number and the account number or the national insurance number and the account number).
  
  This table would not adhere to Boyce Cod normal form because it contains a candidate key (possible primary key) which has a functional dependency on another candidate key (customer ID and national insurance number).

So what went wrong in your case? Your tables looked as if there were in third normal form. But they were not. This is why.

Your transaction is correctly separated from your account but you have not properly separated the types of transaction (which possibly needs another relation - i.e: cash withdrawal, cheque). Instead the transactions were separated into two different tables.

Each transaction table included it's own unique ID but because they were separated out between two tables there was no guarantee that the transaction ID's were unique. There go the relation, although fragmented, was not in first normal form because there was a possibility of duplicate transactions; if it was not in first normal form it was not in second and third normal form either. My advice to you would be to further normalize the data by separating the transaction types into a new table.

Tell your friend that he should read up about normal form. Because although some of it might seem obvious it should be used to double check the way in which you have organised the data. The question asked here would have been the first of many problems had it not have been spotted.

Although normal form is good there are some situations where it desirable to store redundant data and break out of normal form. Forum databases for example may store the post count in the thread table as when the data is retrieved doing the lookup on all posts corresponding to that thread would be costly especially if it contains 50,000 replies (no thread titles mentioned ).

[DigiRev]

So are you saying it should be like this?

TABLE transactions
id
user_id
account_id
transaction_type
the_date

TABLE transaction_types
id
name

ID Name
1 Withdrawal
2 Deposit

What about user_id? Unless I'm misunderstanding what you posted, if the table has 2 or more records with the same user_id it will be "redundant". But I don't see any other way to let a single user have more than 1 transaction.

In his site, each user may also have more than 1 "account" (think of account like a bank account). So that is the purpose of that field.

I think the way I decided on is the best (simply use a negative value for withdrawal, or positive for deposit, since his site only needs those 2). If it needed other stuff like cheques, cash, etc. I'd definitely use a separate table.

Still wondering if there are better ways to set this up? Not really a big deal for this site but I'm always looking for better ways to do things...

[visualAd]

Yeah positive and negative values are fine. My suggestion only referred to the transaction type as in how the deposit or withdrawal was made.

It's perfectly okay to have more than one transaction with the same user ID. But your old set-up could have yielded a transaction with the same account, user ID, amount, date and ID thereby producing a duplicate record.

I used normalization to demonstrate why it was a problem because it is usually a good idea to check that your database conforms to BC normal form and if not weather changing it so it does solves the problem.