relational database 3

[hwkranger]

I'm rebuilding a database that was very poorly designed. It's not relational at all. It's pretty much a bunch of tables that hold information, all thrown together.

Now, I have some issues. I want it to be 100% relational. I want it to be fast. here's some issues;

1) I have about 60,000 tickets. Tickets have about 60 different fields between the 20 different ticket types. There are 19 fields that are basic to all tickets. Not all tickets are related. Cash are only offset by proof tickets. Proof tickets are only offset by teller type ticketse (there's about 18 different types offset them) The other 18 types can only be offset by proof or one of the 18 types of tickets.

Should I have 1 main table for the tickets with the 19 fields and sub tables for the different types of tickets?

or should I have 1 main list table, (has the id's) 3 sub tables for the different 'categories' of tickets, and then 18 more sub sub tables for ticket types?



Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[willir]

hwkranger

I am big on normalization, but some performance issues, sometimes bending the rules a little for nomalization may help. (i.e., when you have to trace back three or four tables to get a frequently used reference number.)

I have a database that seems to have similar issues to yours. Funny enough, it is an IT asset management database. It took me a couple of tries before I got what I think is right.

Here is how it works...

Device Master table
- tracks common things unique to all devices - manufacturer, model, serial no, etc.
- categorizes the device - PC, Server, Monitor, Printer, etc

Then for each category, there is another device table used to capture info for the specific type of device...
- PC -- CPU, OS, hard drive
- Server -- CPU#, CPU, OS, drive config, fault tolerance, etc
-- Monitor -- size, max resoultion...

Plus an IP address table

Basically, if it exists, there is a one-to-one relationship between the device master and the device detail tables.

It has not caused a problem for me, but the design does rely on useing the device category when joining the tables. For example, if I wanted to create a report that provided technical details for each device, I would have to break the report down for each category.

Most of my queries hit the device master table which then pulls in the detail. But I can work backwards, for example, find all Servers running Win 2000.

Designing the form was also tricky. But when stared using a tabular form -- details on other tabs, things came into place. If I get time, my next task will be to cut back on the tabs and make subforms and some tabs visible or invisible depending on the device type.

I believe the design is failry well normalized, and the design resulted in a lot of unexpected bonuses -- I was asked to add some functionality, such as escalation, support, procedures and contact info. Because of the design, implementing the changes was incredibly easy. (If I had used an older version of the design, implmenting the changes would have been extremely tough.)

Getting back to your "tickets"...
If your tickets types are different, such as my example - device and device type, then this type of design with a one-to-one relationship may work.

However, this design is more complicated than the more typical one-to-many. If you can get by with a simpler design that bends the rules just a tad on normalization, then it may work out a little better.

Main table ticket table (tblTicketMaster ??), it sounds like the 19 fields should be included. Remember to link data from another table where requried. From your description, the ticket type and or ticket category should also belong on this table. You will need this info to link to the second table.

Now what tables to use after that depends on the differences. You do not want to end up where you have to continuously check for category and ticket types when performing calculations, and reports.

Are tickets in one category fairly similar? If so, then using a ticket table based on category may suffice - three tables. Logic on your ticket master form would display the relavent subform based on ticket type. Queries and logic for each category may be required to extract the specific info for your reports.

If the tickets are too different from each other regardless of category, then the category may provide useful group information but is not important for the actual ticket tables -- you may want to use one table for each ticket type. The form may get a little messy, but Access does support the use of different record sources for the same form and report which may help you out a lot if the fields are similar.

I hope this rambling helped.

Richard

 

[hwkranger]

Willir,

Your advice is excellent. I understand what you're getting at completely. For me, though I 'must' bend the rules of normalization when using acccess because of the way the queries are run. (tables are returned to users computer, users computer runs the query)

I believe that I found my answer. What made it tough, was a friend of mine does SQL server and we were talking about how my Access file should be designed. He believed that ALL tickets should be in 1 table, with fields not common to all tickets in sub tables. Which makes excellent sense - but.. Not all tickets are related to each other. Cash tickets have nothing to do with Teller Tickets, a cash ticket is always offset by a Proof ticket. And no type of ticket can post to a cash ticket. (to fix a cash, a proof has to be made, then a cash makes an offsetting proof and the two proofs offset each other)

Since not all tickets are related to each other then they do not truly represent a single entity. (IMHO) Thus, I'm representing the tickets as 3 subtypes, and then the fields not common to all tickets end up in sub tables.

Thank you for your advice. If you see anything wrong with my logic or reasoning please feel free to tell me.







Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[TheAceMan1]

How are ya hwkranger . . . . . .

I can easily see you root tables as follows:

tblType
*******
TypeID
typName
19 Basic Fields

tblTickets
**********
TickedID
TypeID

I can't complete the other tables because I'm unsure of your statement : Cash are only offset by proof tickets. Proof tickets are only offset by teller type ticketse (there's about 18 different types offset them) The other 18 types can only be offset by proof or one of the 18 types of tickets.

For instance, "Cash are only offset by proof tickets", what does this mean? Particularly the term "offset". Can you be a little more specific, as I'm sure it affects the layout of the rest of the tables.

TheAceMan1

 

[hwkranger]

It works like this:

Imagine 3 accounts (Teller, Cash, Proof) and like 18 small accounts.

Every ticket has a corresponding ticket, or it will. Everything works in Debits and Credits. Debits offset credits and vice versa.

Cash Tickets can only be 'offset' by proof tickets (meaning a cash credit is related to one or many proof debits) Thus when a cash ticket is made, one or more proof tickets are made. Same works for teller.

Now Proof tickets then must have a related ticket. Proof can NOT relate back to cash, but they may relate to teller.
They may also relate to other types of tickets.

There are only 4 ways tickets are generated. From Cash being adjusted, Proof being adjusted, Teller being adjusted or from Closing a Proof ticket (to one or many other accounts)

These 4 ways generate 12 different 'categories'
Cash Adjustment - ticket making the adjustment
Cash Offset - ticket related to the adjusting ticket
Proof Ticket
Proof Adjustment - Ticket adjusting proof
Proof offset - ticket related to a proof adjustment
reject - a ticket that was rejected (cash, teller, or proof)
Research - any Ticket related to a Proof Ticket
ResearchOffset - Any Ticket related to a ticket related to a proof ticket
Teller Adjustment - a ticket adjusting the teller account
Teller offset - a ticket related to a teller adjustment

As you can see, it's not really sorting apples and oranges..

Thus I have a tblTicketListing that lists ALL tickets. With 3 Subtype Tables: Proof, Teller(and other), Cash. With 7 Sub type tables to Teller for the 'other' tickets.

I hope that clarifies a little... I greatly appreciate the help with all this. Normally I'm excellent with normalization, but when it comes to super types and sub types, I get lost in whether they should be separate, or together, especially when the subtypes are related.


Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[hwkranger]

(Another explanation I wrote)

Well,

Sorry for not being clear:

Everything works in Credits or Debits.
Imagine 3 main accounts (proof, cash, teller), and like 18 other misc accounts

There are Cash in, and Cash out. All Cash goes to proof.
(cash is 1 account, proof is another)
Proof is a holding account, proof can not have a balance therefore all proof must be moved.

Proof is moved to the other 18 misc accounts.

The tickets are what moves the money.

So, if there's a problem with then, money is pulled from Proof to cash. Then for proof to get it's money back, it's pulled from wherever the money should have come from.

But if Teller is out, proof is hit also. Then proof regains it's money by regaining it from one of the many other accounts.

That's why I split my tickets into 3 tables, Proof, Teller, Cash. (i'm still unsure if it's the right way originally EVERYTHING including 76 fields were in 1 table)

Everything goes to proof, then proof regains it's money from a pluthera of other accounts.

The idea of the money and account's doesn't really matter here, Mostly tickets. Cash Tickets have a corresponding Proof Ticket. Teller Tickets have a Corresponding Proof Ticket. Proof tickets are cleared by 18 different types of tickets (which can be a teller, but not a cash)

If I put ALL tickets in the same table, then I have to relate the table to its self. To make things much more complicated - a Cash ticket can be related to multiple proof tickets, a teller ticket can be related to multiple proof ticketes, and a proof ticket can be related to many types (Not cash) including other proof tickets.

Meaning for a cash ticket there is a mandatory one to optional many proof tickets. For proof there are mandatory one to optional many proof tickets. Mandatory one to optional many Other Types of tickets (not cash). For teller tickets there are mandatory one to optional many proof tickets.

I hope this is a little more clear.



Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[willir]

So basically...

The ticket master file will be a transaction file - money in and out, plus whatever the ticket is tied to.

But you also want to specifics for each ticket type / category.


In general, I would have thought of using a G/L transaction type of solution and use different G/L accounts for ticket types. The G/L transactions would be stored in each account using standard accounting practices -- double entry / credit & debit. Then reconcile the tickets in a separate table. Since the ticket would involve more than transaction, the transaction would store the foreign key for the ticket. This way, you would be able to track the transactions for a ticket.


Another system that seems similar is the movement of stock -- stock in / out, split, exchange, cash in / out, dividend payments, etc. Again, the transactions are stored in the primary table which will deal with the number of stock, not the cash (since we all know that the value of stock can change). The selected activity for the stock transaction determines the rules to apply for the transactions.

It would almost seem that the "rules" are hanging us up, and transaction types properties are being stored in different fields. Should they just be categorized, and use less fields??


Since you seem to have exposure to banking, I have to assume you addressed these more typical approaches and have moved to beyond them. After all, 60,000 transactions is nothing to sneeze at.


Oh yea, one trick I frequently revert back to.
Create a design and enter some data. (Dont worry about the forms). Then run your queries to see if you get the information you require. If it works, then you are on the right track. If not, then you saved yourself a zillion hours finding out the problems after doing the work.


I will re-read your post and do some more brain storming.
Richard

 

[adsfx]

willir
have just created a tech inventory DB that sounds similar to yours, but did think for a long time bout r/ships.
the bit i had trouble on was linking the categories to the main table

tblmain > tblcats > tblspec?

in the end the main table has the fields for hdd, cpu etc..
as the main items are pcs/servers and any fields that are gonna be the same are linked to lookup tables

i dont think this is the ideal solution any chance of a screenshot of your relationship table?

cheers Mark

 

[hwkranger]

The whole mess of things come from both the rules, and the way the information comes.

Information every morning is downloaded from Main frame reports. These reports are parsed and are used to build information on branches.

The tricky thing about all this is what way you view the entities.

I can view them as the following:

Branches have accounts, accounts have balancs, Accounts have adjustments, Adjustments are made by tickets.

or

Branches and tickets.

The previous person said, "Branches and Tickets." Which is fine and dandy I suppose, but there are 400 branches which generate up to 50,000 tickets a month. We have 2 1/2 months of work at any given time in the database.

The people in charge don't want any interface changes what so ever. They want it to look the same, but I can change whatever I want behind the scenes to improve speed and reporting.

Speed is the main problem. You can imagine why when sometimes there's 100,000 tickets (not often - mostly under 50,000 but peeks to 100k) and 20 people in an access database.

Because the users are on 300 mhz pentium 2's, I'm trying to keep everything efficient for them as well as have a good design.

What I've done is have a branch table. Branch Balance table (balances downloaded on 3 different accounts goes here - which normally I would have a separate table for accounts, because you don't want to add fields if you add accounts) and 3 tickets tables. (3 main different types of tickets) and a ticket master table, and 5 sub tables for similiar other types of tickets.







Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[willir]

Mark / adsfx
Post your request as a new post.
- I dont readily have a web site to embed the graphics (besides, it does not fit on one screen anway)
- It is not convenient for me (yet) to use a public email address.
- Using this thread for your discussion would detract from Randall's issue.

I will post parts of my schema if this is what you are looking for. (It will show relationships and key, etc)

Randall Still pondering your issue. Your most recent post was insightful.

Richard

 

[TheAceMan1]

How are ya hwkranger! . . . .

Sorry to post back so late. But I have done quite a bit of brainstorming on this.

For the most part, I fully understand your explanations and the results your looking for. However I,ve run into a brick wall of sorts.

You said : Thus when a cash ticket is made, one or more proof tickets are made. Same works for teller. You then said : Proof can NOT relate back to cash, but they may relate to teller.

The logic above is circular, making it unsuitable for database use (at least in its virgin form). It is here that I've come to a standstill. If I can find a way out or around this, I believe your required table structure & relationships will present themselves more readily.

Gotta get back to the lab. Will post again after next brainstorm! . . . . . . .

TheAceMan

 

[TheAceMan1]

How are ya hwkranger! . . . .

Sorry to post back so late. But I have done quite a bit of brainstorming on this.

For the most part, I fully understand your explanations and the results your looking for. However I,ve run into a brick wall of sorts.

You said : Thus when a cash ticket is made, one or more proof tickets are made. Same works for teller. You then said : Proof can NOT relate back to cash, but they may relate to teller.

The logic above is circular, making it unsuitable for database use (at least in its virgin form). It is here that I've come to a standstill. If I can find a way out or around this, I believe your required table structure & relationships will present themselves more readily.

Will post again after next brainstorm! . . . . . . .

TheAceMan

 

[hwkranger]

I guess I used the word 'relate' perhaps as a bad choice for a word.

If different types of tickets are entities, then it would be like this.

1 cash adjustment creates mandatory 1 or many Proof Tickets.

1 teller adjustment creates mandatory 1 or many Proof Tickets.

1 Proof Adjustment creates mandatory 1 or many proof or any type of tickets not cash.

1 proof ticket is cleared by optional 1 or many teller tickets or Proof ticket, or Any mixture of tickets other than CASH. (this is where I used the word relate in a bad sense.)

All Adjustments Are tickets, but not all tickets are adjustments.

Essentially, All Adjustments create proof tickets (except for proof adjustments, they don't have to create a proof ticket). All proof tickets must be 'cleared' (have an offset), no proof ticket can be offset by a cash ticket.

What makes this seem weird is that Cash can effect proof, but proof may not effect cash directly. For example, (this isn't a real example but rather a metaphore) You have 2 boxes of money, if you want to move $100 from the one box to the next, you'd have to have a 3rd box of money called proof. You'd take $100 from proof and put it in your 'in box', and then take $100 from other cash box and put it in the proof box. This example creates 4 journal entries, Cash out from cashbox 1, Cash in to Proof, Cash out from proof, cash in to cash box 2. The two entries that are related to each other would be the Cash in to Proof and the Cash out from Proof.

The Only Journal Entries we are interested are the Journal Entries that happen 100% inside of Proof.

This is sort of how teller works, only difference is Teller Should always goto proof, but these rules change from time to time, so I can't make it static.

I'm trying to explain only the major rules, since essentially, I'm not really working with 3 accounts but rather 25ish. There's 3 major accounts, and I can group the rest of the accounts with Teller (they function like the teller account, thus their tickets work like teller tickets)

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[TheAceMan1]

OK hwkranger!

Crystal clear on your return post. Will get back to you as soon as possible . . . . . .

TheAceMan

 

[TheAceMan1]

How are ya hwkranger . . . . . . . . . .

Forgive my late post. I've spent several evenings with two colegue's of mine going over your problem. Bear in mind that we are not bankers(not having your knowledge has proved to be a true handicap), and as you have stated in earlier posts, other rules lie underneath as well as other tables. Nevertheless we were able to logically attack the problem and surface with a major conclusion. We believe its the only direction you can take and we invite the opinion of others who may be reading this post.

We found that your rules (some of which can change and as I'm sure your already aware) prevent any static solution, its just not possible. As we treversed through what appeared to be basic tables, a particular rule would change Placement & Relationships. We kept coming up with this, time and time again (keeping in mind that reconcillation of your "Proof Table", is the primary target).

The only other handicap, was the fact that you say you have to keep the same interface. This was more restrictive than anything else. When you do resolve, we query if the same interface is possible. We believe the origional database was flatline because of the rules, but it did make the interface alot eaiser.

Our direction of resolution is therefore this : Look into Dynamic Relationships. Its the only way you can circumvent any rules you may have. Espcially if rules are changing!. The only problem with this, is identifying when a relationship should change (bearing in mind that changing relationships through queries could save you alot of work).It could be easy, or it could cause you to pull your hair out. Also be aware, it could cause you alot more programming than you anticipated. ALOT MORE!, espcially since you have so many tables. Either way, [bold]resolution can be achieved in this manner[/bold].

The above is just a small critique of what we went thru. We have certainly done the best we could(under the circumstances). We salute you for taking on such a formidable task, and wish you God speed in obtaining resolution. If you do resolve(we think you can!), could you let me know how maintaining the same interface came out!.

Horace Aniton AKA TheAceMan
Bobby Vaicels
Kenny Givins

Members of ProTech Consultants



TheAceMan