Single or Mutlible tables?
-
Hello,
Can anyone help me with this ?
I am designing a business solution.
Previously, I had each data contained in a spereate table , for example; vendors and clients. And so I did for the transactions like invoices .. etc
But I am now thinking of having all the data into a single table while using flags to distiguish between vendors and clients. And also use another table for the different transactions
Regarding perfomance, will performance be affected specially with large data (like 1 million records?)
Thanks in advance.
-
Hi Sameh,
I think that would not affect the performance much.But be sure that you create proper indexes to make sure that ur data retrieval is faster.
Regards,
Vasanth
-
Thanks Vasanth
I read that having unncessary indexes would actual criple the database performance.
What do suggest for the optimal use of indexes?
-
While it is true that too many indexes can cripple database performances (with many inserts and updates on the table), properly created indexes (like for foreign keys and there matching primary key) will greatly improve performance on large table when an inner join is required.
I have never tested joins on tables with over 1 M rows but in my own experience and from what I have read on this forum, you don't seem to have sufficient reasons to denormalize the design. Also the speed improvement, if any, should be almost insignificant compared to the properly indexed normalized design.
Might I suggest you read about indexed views?
They are basically tables physically kept on the drives that are updated when any row on any table of the view is modified. This could be the performance boost you are needing. But keep in mind that any change done in the underlying tables will have to be done in the indexed view, so the server will have to double the workload on any insert, update and delete.
-
May I say that you're about to compare apples with oranges?
Each table models a single entity which is described by its attributes. An invoice is no offer, a client is no vendor, they are logically different things and should be modelled as such in your db. This is no question of performance, but rather good db design.
Curious what rant Joe is about to deliver here!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Can't wait to see it too.. and Yoda's as well :-).
-
Well Frank,
I already have an appplication which has each of clients and vendors apart.
But I had to have an account code for each of them in the chart of accounts. When a transaction is being made a had to create a record in the General Ledger table.
Later I figuerd that all clients , vendors, banks should be a part of the chart of accounts.
Also I thought of having all different transactions to be in a single table as they all create a Journal in the General Ledger.
So , why create a different table for each transaction if I have to create a record for in the General Ledger table anyway?
So I decided that it is better to have all accounts (ie; vendor and clients) in a single table and have all the transactions in another (ie; invoices and vouchers) which would compact the database and lessen the possiblities of errors that would occur when I have to insert or update information into the General Ledger table!
That's the whole idea.!
Do you buddies have any suggestions on that??
Thanks
Sameh
-
Now you seem to be comparing oranges with tangerines. Getting closer but it's still 2 different things. I'm currently working with a denormalized DB and it's a big pain in the @ss. Why not keep things simple?
If you have a vendors and clients in the same transaction table you'll need a way to differenciate them.
You can either have a field vc_id (vendor/client) and an id_type to know if it's a client or vendor, but you'd have to have 2 columns in the index instead of one and always have 2 where conditions to query the table (with all the overhead involved). Or you can skip this field if you know that the client ids and vendor ids are unique to each other (meaning that cliend_id = 1 forbids to have vendor_id = 1 which would be one more validation to make and another thing that can break). But these methods doesn't allow for foreign keys so you'd have to make sure that the clients and vendors really exists and do manual deletes in this table when the parents are deleted.
Or you could have 2 columns : client_id, vendor_id with a constraint check that makes sures that only 1 but always 1 of the 2 is inserted. This would allow for foreign key constraints, but this is the equivalent of adding a column that stores only null values in the table (because you always have 1 null)...
I don't see the space nor performance gain in any of those cases... and the coding will take more time to do and debug.
Or you can have 2 transaction tables and simply do a query in the table that contains the data with a single 1 colum index (which is easier to maintain >> less overhead and diskspace).
-
There are some very good articles here and elsewhere about why you should not create monolithic tables - 'Lookup Table Madness' from Sept comes to mind. There are also some good articles (and chapters in various books) on normalization.
I have worked systems that were not created by a DBA or anyone remotely playing one on TV. They were born from Access databases that someone in the office threw together and then someone else came in and wrote code, and someone else convinced them it would work better in SQL Server and someone else convinced them a DBA would make it work, and there I am, looking at the stonehenge of databases, a series of flat file tables that just happen to be clumped together.
Sigh....
And, yes, I have my feet up and popcorn ready for when the database design gurus speak...
Quand on parle du loup, on en voit la queue
-
A financial accounting system indeed requires a very well-designed database. However, I think most systems here mix the pure OLTP data with presentational stuff. A journal is such a presentational issue. It can be achieved either by directly querying the transactions table or via several intermediate tables. We're running SAP R/3 here and if I am not completely mistaken, SAP uses several staging tables before they finally present an accounting journal on the screen. The originally underlying data is still very normalized and separated from each other in different tables. I am not aware of the article Patrick (PBirch) mentioned, however I did think more of this one here:
http://www.sqlservercentral.com/columnists/dsumlin/lookupstrategy.asp
I did a reply there which includes this link
http://groups.yahoo.com/group/sql_server7_forum/message/5731
It contains one of my all-time favorite rants by Joe Celko. Please don't take it personally and/or offending. I think, although about lookup tables, it can also be applied here. Get yourself a coffee, sit back and enjoy reading it. It pretty much hits the nail on the head.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Believe me, I got more dazzled with this discussion.
I still cannot decide what to do.
Having one lookup table will help me having less code on the application side.
The pros for me are still:
I will have a single form which will handle all the transactions while only havig a "case" statment for each type of transaction.
I will not need to update the main Journal table for each transaction.
The cons:
I have to rewrite the entire code for the application I already have!!
Please give me more ideas and real life examples!
-
You just answered it yourself, if you need to rewrite the whole application to allow for a design change that is not needed... and not approved by anyone here. Then why do it?
If the tables are so similar then once one query is done then the other will be the same just with another table name. Then you can simply choose which query to use on the client side instead of doing the case statement in the query. That way you can still code only 1 form instead of 2, so it's not that much more work fr you... assuming it's more work.
It's just a matter of doing the right work at the right place and time.
-
Jumping into this discussion late. Clients and Vendors are different entities from one point of view. From another point of view, aren't they both companies?
There is much about a company that is the same regardless of whether it is a client or a vendor. Both have a record in the chart of accounts. Both have physical locations, employees, divisions and departments, websites, etc.
With this latter view, in OO terms, Clients and Vendors both inherit from Company.
Wouldn't normalization require that company data not be repeated in different tables? Especially in the case where a Vendor is also a Client. Any data that is specific to a Vendor should be stored in a Vendor table with a CompanyID field, and likewise for a Client.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply