Too Many Tables???

Question

I am creating a database. One of the tables will be a transaction table which will hold all the daily transactions. With in the Transaction table there will be a column for Location another for the type of transaction it was another is for the Unit Of Measure (Pounds, Liquied Ounces...).
My question is should I put in the actual values for Location, Transaction Type and Unit Of Measure in the Transaction table.
Example:
Transaction Table
-----------------------
TransID     TransType     Location               UOM
1               Receive         Warerhouse           LB

OR should I make Reference tables for each on the Transaction Types and Locations and UOMs
Example:
Transcation Type Table
---------------------------
TransTypeID     TransTypt     TransDescription
1                       Receive         Received
2                       Deplete         Thrown Away


Locations Table
--------------------------
LocationID         Location         LocationDescription
1                       Warehouse           The Warehouse
2                       Office                   The Office

UOM Table
-------------
UOMID          UOM              UOMDescription
1                    LB                Pounds
2                    LO                Liquid Onces

So the Transaction Table would not look like this
Transaction Table
-----------------------
TransID     TransType     Location               UOM
1               Receive         Warerhouse           LB

BUT like this
Transaction Table
-----------------------
TransID     TransTypeID      LocationID             UOMID
1               1                       1                          1


Can you tell me the pros and cons on doing it this way? Is proformance any better either way. For indexing would it be better to index off an integer or a nvarchar or does it matter?
Any help would be great thanks
Matt

Answer 1

Hi mutlyp

Its not performance losses so you keep this way.

if you  need this way Structure means Create View Table its will help performance. and reduce the coding whenever u need..

Transaction Table
-----------------------
TransID     TransType     Location               UOM
1               Receive         Warerhouse           LB



Ex :
CREATE VIEW Sample
AS
SELECT  TransTypt, Location, UOM 
FROM Transaction  TR
JOIN  TranscationType  TT ON TT.TransTypeID    = TR.TransTypeID    
JOIN Locations LO ON LO.LocationID = TR.LocationID 
JOIN UOM  UO ON UO.UOMID =  TR.UOMID


After finished.


When ever u need call this ViewTable Like : SELECT * FROM Sample
its will shows

TransID     TransType     Location               UOM
1               Receive         Warerhouse           LB


--> Manigandan-DBA , Mark as Answer if it helps!

Answer 2

Hi Manigandan
Thanks for the reply. It made a lot of sense.
So are you saying that doing your way is better than doing it the other way? If so would you please tell me why?
Also are you saying that when I do need to get this info from the database I should use the View example you showed me? Why is using the view better than using a select statement inside a stored procedure?
Again thank you for helping me understand this.
MutlyP

Answer 3

Hi

I respectfully disagree and purely on the grounds that your reference data will not be updated as often as your transactions --- >You mention that this is a Transactional Database. Further there will be a tremendous waste of space due to repeated and redundant data, which leads to the obvious question of normalization. Transactional databases should be well normalized.

With this in mind,  you should be aiming for at least 3rd Normal form structure for the transactionl tables(See Codd).

In short, there has got to be a better way to do this.
What YOU DO have is an excellent set up for an OLAP / data warehouse system with a plethora of dimensions with the fact table being the actual transactions. THIS IS TRULY GREAT.

Finally, I would use a modelling tool (e.g. ERWIN)  to ascertain an optimal structure BEFORE commiting to either method discussed above. Remember you have to live with the results.

If you need more help, please feel free to contact me at <email address removed for privacy>
sincerest regards
Steve Simon

Answer 4

The author and I chatted on the phone about this one. I believe that it can be closed.
s

Answer 5

OK; but one of you needs to describe the resolution.  While chatting with the originator may have indeed resolved the issue it does not present anyone that re-reads this thread with the information of how this issue was resolved.

Answer 6

You are right I hate it when there is no answer to these questions. My appologise.
What Stefaans and I decided that my original schema was the correct one. Using Reference tables is the best way to go. Mainly because the reference tables will not be getting updated as frequently as the other tables that hold the majority of the data. So when searches are done  to retieve the data the search will not be bogged down by the reference table data and space will also be better because instead of full strings in the cells there are numbers that refrence the reference tables.
Like I said that is what Stefaans and I concluded. Not saying we are 100% right but makes the most sence.

Thanks for all the help.
Mutlyp

Answer 7

Kent

As Mutlyp mentioned, we had a good chat on this one. I do agree that the solution should have  been placed on the site. For this I apologize.

The transaction table has a "companion"  table called translog_2010 which contains many of the fields of the "Main" transaction table. However this the "log" table (WHICH IS NOT A TRUE LOG) is the only table that contains the foreign keys to the transaction type table, the location table and the Unit of Measure table.

I am afraid that he is stuck with this one.

The EMIS table is linked by a one to one relationship to the Main Transaction file. There are no apparent better links that one may utilize. I am afraid that with out major reworking MatlyP has little choice.

Once again, sorry that I did not fill this in properly.

sincerest regards

Steve