DB size?

[Pat Hartman]

Here's the last 6 that I have worked on:

	AMS	BCR	ATU	ERC	OE	CMM	
[U][B]Type	Count	Count	Count	Count	Count	Count	Average[/B][/U]
Form	82	31	21	36	46	65	47
Macro	3	4	2	4	4	4	4
Module	16	7	2	6	4	8	7
Queries	262	218	23	169	207	220	183
Reports	49	8	3	59	31	46	33
							
Table - Linked Access Tables	2	3	0	19	27	39	15
Table - Local Access Tables	142	2	17	7	2	2	29
Table - Linked ODBC Tables	0	47	0	16	0	0	11
All Tables	144	52	17	42	29	41	54

Here's a link to where I posted the object type table and the query that counts objects.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=7463&highlight=msysobjects

 

[Ian Mac]

But you have to handle the 20 categories.

There are basically three sections.

In one table all the prospects are strored and one field has a number from 1 to 20 for the category and another field has a number from 1 to 15 which identifies the salesman with the prospect. Thuis salesman number 4 might have prospect categories 4, 11 and 16.

There is another table which has the various calling and selling goals for each day of the week. There is another table that takes this data for each day so that a running total is obtained. The data is physically appended since that allows calling goals to be changed between a starting date and finishing date. Thus allowance can be made for holidays etc.

There is another table that a new record is created when each call is made.

There is another table that holds summary data of the above table because the above table gets too big. The summary data makes one record for all the calls on a given day for each category.

Then there are 15 tables for a diary for each salesman. Appointments are stored in one table but the 15 tables display appointmenst and allow appointments to be made for the appropriate salesman.

There are 22 queries, 26 forms, 24 macros and 2 tables for each category. All of these replicated 20 times and of course with different names.

The data in each table is all different because the various calling goals are different for different categories of prospect as are the starting and finishing dates.

In addition there are another bunch of queries, tables etc that keep track of prospects being consumed and also replacing the names from a large holding table.

The actuall calling is done from one screen and when a category of prospect is selected then the record source is changed.

Even if you could integrate it into one that would not be successful because quite often a single category is hired out. I make a blank data base and just import the queries, macros etc for one of the categories plus the other objects that do the statistical results.

Mike

Firstly, I 100% concur with Pat, your data IS NOT normalized at all:

Just an insight, but, you don't need 15 diary tables!!!! One flat table will do this job just dandy!

Unique ID: our key for indexing
Salesman: System ID (a number hoopefully)
Date: the date
Time: the time
To do: what to do
Etc.

That would do lovely for every salesperson.

Access doesn't care about the amount of records it needs to look at be it 1 or 100000, just that it's got the smallest number of bytes to look at.

So the above would be
Number
Number
Date/Time
Date/Time
Memo (hopefully just text)

running a query on this type of table will be FAR better than looking at 15.

(BTW I have a huge database which has 1000+ employees on it, apart from the unique identifiers NO information is repeated in any of the 20 tables that record DIFFERENT info on them, the queries do that!)

back to the question:

A DB of 1mb or 100mb will work if the data is properly stored, not repeated, given the smallest type of data to accommodate it (i.e interger is smaller than single, acn iI use an interger? etc.) and indexed correctly.

So, I wouldn't worry about the size of the DB, it's what happens at run time.
If you have 3000 forms, say, this will be a big DB Megabyte-wise. That doesn't matter it's how much memory is being used at runtime.

hope this helps.

 

[Franknstuff]

somebody has been busy

Thanks Pat that's a baseline.

I guess without my dry-eraser pen and white board I don't explain things very well?

 

[Mike375]

Ian,

Perhaps if I elaborate you will see why I use 15 tables, BUT if there is a better way and I gain then I change. Of course one must always consider the time and effort to impliment the changes.

Each of the 15 tables are identical except for the salesman number. There is one query for each table and each query simply has one calulated field.

Each table has 365 days entered. Each table has 20 fields for appt slots. 10 for the appointment time and 10 for the expected finising time.

However, the actual appointments and details are stored in one table. A double click on one of the fields that has the 20 appt slots will open a form for the corresponding entry in the appointment table.

When I click OpenDiary on the cold calling form then the diary form opens and the record source is set for the salesman associated with that prospect.

If I place all 15 tables into one table I finish with 5475 records. However, for some type of calling we stick 2 years of dates in a table. It is just so much simpler to do that in the specific table and doubly so when I am getting someone else to do it over the phone. I can also tell you that on older slow machines the diary is too slow to open with 5475 records. Also, I often hire out part of the data base for telemarketing and that might involve only 1 or 2 categories. All so much simpler when it is split up like I have it done.

It is also much easier with 15 separate tables for the caller to email me that table. I just have some macros made with names of Salesman 1, Salesman 2 etc and those macros export the table in question to .mdb file with nothing else in it and the caller emails to me that .mdb file.

Mike

 

[Pat Hartman]

Mike, I'm really not trying to pick on you but have you noticed how much time you spend defending this database? I don't believe that there is an experienced developer here who would agree with your approach. I believe that it is a disaster waiting to happen but you understand it and are happy with it so, peace be with you. You don't really want to hear what we have to say. Every time someone offers a suggestion, you say "yes, but". So, OK, don't change anything. You just stand firm with your methods and keep making 20 of everything and we'll stand firm with ours and make one of everything and control data display with parameter queries. None of us has to add a complete set of objects when we add a new sales person or product. In our databases, the user just adds a new row to a table. No developer needs to be involved.

 

[Mike375]

Pat,

From your posting:

Mike, I'm really not trying to pick on you but have you noticed how much time you spend defending this database?

I defend it because it works and you and others attack it. If you continue to attack I will continue to defend and because it works. It is developers from insurance companies and consultants to insurance companies that come and see me about "what is the attraction to this DB"...so I must be doing something right. Comments like Rich made about "use a cross tab query" to sort across the row"...well I would like to see that done for what is needed. But you professional developers disagree. Not long ago I posted up a question on how to sort across a row and you responed with, as near as I can remember, Access is not a spreadsheet and I would be on my own. I asked the question because I need to make another version (totally unrelated to the diary) and there may have been another way. Rich says it is easy, just do a cross tab query.

I don't believe that there is an experienced developer here who would agree with your approach.

That has been demonstrated and is no suprise to me.

I believe that it is a disaster waiting to happen but you understand it and are happy with it so, peace be with you.

What disaster will happen.


You don't really want to hear what we have to say. Every time someone offers a suggestion, you say "yes, but".

I have to "Yes but" when that is the case. I did not do that when Raskew (I think that is the handle, name is Bob) put up some wonderful stuff on dates. I did not do that when someone on dBForums showed me how to convert a mixture of

7032004
23032004

into 7/3/04 and 23/03//2004 with one calulate field as opposed to my Count filed and then the IIF fields with a Mid fucntion and then a join.

Much better than what I was doing. Have I made the change...No. Why? because it make no difference to how the thing works and it would take some time to make the changes. But I kept it and when I next have do something I will use it. Same as Raskews way of getting "age" as compared to how I do it.

So, OK, don't change anything. You just stand firm with your methods and keep making 20 of everything and we'll stand firm with ours and make one of everything and control data display with parameter queries.

I live on parameter queries with reference to the form. No argument there.

But let's get down to the nitty gritty. Ian says I should have the 15 tables that show diary appointments in one table. We now have a query based on a table with a minimum of near 5500 records. If we have 30 sales people it will be 11000 records and if we put 2 years of dates in there we could have 22000 records. In my case it would take me all of 5 minutes to add another 5 tables and them do Replace to change the salesman number. It would take me another 2 minutes to go to the macro that opens the diary and copy the last 5 macro actions and then change the conditions to the new salesman numbers such as 16, 17, 18, 19 and 20 and also change the Expression in the macros for RecordSource from 15Query61 to 16Query61, 17Query61 etc.

I already know that a macro can run down piles of lines and each line has a condition and the macro will fly throught that. But if I used Ian's suggestion of "all in one table" then things would get vey slow on a slow machine.

None of us has to add a complete set of objects when we add a new sales person or product. In our databases, the user just adds a new row to a table. No developer needs to be involved.

I would love to see how you could do that by adding another row in relation to the diary system.

If you can show me a gain then I will embrace it.

What I have to happen is that when the diary opens it opens for that salesman and the first line showing is the current day. It needs to show his appointment slots for the next 365 days. It also needs to show me the total number of appointments made.

If the prospect agrees to an appointment at 2.30pm on June 17 then it needs to sort that row after the appointment is entered.

As best as I can see I either have a table for each salesman (as I have) or a huge table as Ian suggested. Again, the actual appointment details are all in one table.

When the diary form opens there are a lot of unbound text boxes in the header for name, address etc that have their values set from the cold calling form. If some of those are wrong ( afault with our prospecting), then changes are made there and when the diary form is closed the values in the calling form a set by those values. The header also has an unbound text box where different details about the appointment are entered. This all goes into a new record for the table that holds all the appointment details.

But I am keen to know how an extra salesman's appointments can be displayed for the next 12 months by adding one row.

Sure, one row can call up some something, but what will it call up. How do I get 365 days of appointments display for the extra salesman off one row.

Mike

 

[Mike375]

Pat,

Two things you might think about.

The starter of this thread, Franknstuff, has stated that I was the only one that responded to his/her question. Professonal Develpers don't listen.

Secondly, I have had more Professional Developers look at this data base than there are grasshoppers in a wheat field. I have done that because my thinking has been I may sell it one day. BUT, they all run to water.

For example, the coding does not do the counterpart of the real long running macros. We have things close down. Coding seems to be very sensitive.

In short, as a "Professional Developer" you would increase your knowledge by listening to some amateurs rather than expending energy on condemning them.

You are obviously an intellignet man and as such you must surely realise a data base like mine that has evolved from 1996 has had a fair bit of input....especially given that I am in the insurance business and there is no shortage of programmers in the insurance companies and banks. Just a thought for you.

Something else you might consider is that this is a discussion forum. I am sure some different people on the forum will gain more by me posting "Yes but"

Mike

 

[Rich]

Your logic is flawed. You don't need tables full of redundant dates, Access has a cal control to select dates from.

Since you had a95 that version had a PIM example db. If you search here you might find it, that's assuming you want to see a more correctly structured example.

Pat's a she by the way
Oh! and I never said it was "easy"

 

[Mike375]

Your logic is flawed. You don't need tables full of redundant dates, Access has a cal control to select dates from.

Since you had a95 that version had a PIM example db. If you search here you might find it, that's assuming you want to see a more correctly structured example.

Pat's a she by the way
Oh! and I never said it was "easy"

But instead of some one line answers how would you replace what I do with the 15 tables and also, how would your cross tab query do the sort across the row....and I might add with a permanent situation that is back in the table. You are saying that your solution is all better etc. but you have done no more than some one and two line answers.

And Oh! and I never said it was "easy" ....then why not have it easy. But let's see your solution. One table instead of 15. And sort across the row with the cross tab.

And I can't wait to see Pat's "one extra row" for the extra salesman. I just want know how that works

By the way, I did no more than respond to Franknstuff's question....others decided to attack

Mike

 

[The Stoat]

Hi,

Tell you what Mike you right me a plain English description of what it is your trying to do. I will design you a full normalized, spanking ass database.
We'll work some way out of transfering your data into it - god's that will be a nightmare . I will get my solicitor to draw up a contract and you can flog it to these interested parties. I'll take 30%. Serious no BS. I've written plenty of DB's for all sorts of people. I'll have to do it in my spare time but what the hell. I'm getting married in Oz in November up at Cairns, i'll be going to Sydney on the way so i'll pop in and see you with it if you like.

TS

 

[Rich]

You've already been given examples and if you searched here for the PIM example db as suggested you could see for yourself how to approach your problems

 

[Mile-O]

Anything worth doing is worth doing it right (no offense)

Indeed.

In response to the original question, there is no average size - it depends on the purpose of the database. As Pat says, a minimum of one query per table.

AS for mike's, I get horrible images of it jumping up with Enter Parameter Value over 20 different queries each whenever a report or form is opened when one query can easily reference the parameter for all instances.

It may do the job for you mike but how much time do you spend maintaining it?

 

[Mike375]

Hi,

Tell you what Mike you right me a plain English description of what it is your trying to do. I will design you a full normalized, spanking ass database.
We'll work some way out of transfering your data into it - god's that will be a nightmare . I will get my solicitor to draw up a contract and you can flog it to these interested parties. I'll take 30%. Serious no BS. I've written plenty of DB's for all sorts of people. I'll have to do it in my spare time but what the hell. I'm getting married in Oz in November up at Cairns, i'll be going to Sydney on the way so i'll pop in and see you with it if you like.

TS

TS

Tell you what Mike you right me a plain English description of what it is your trying to do.

Well, I can already do it all in terms of what the data base does. But if I do as some people have suggested in the insurance business and sell it or sell parts of it, then it is going to need to pass the Pat Hartman test at the institutions. Whether it works after that would not be my concern.

I will design you a full normalized, spanking ass database.

You don't need to design anything, that is done and it has been running for a few years. As I said above it would be mainly an issue of it passing the Pat Hartman test with the institutions.

We'll work some way out of transfering your data into it - god's that will be a nightmare .

That part would be very easy to do...of that I can assure you. This thing is put together much better than some my attackers think.

I will get my solicitor to draw up a contract and you can flog it to these interested parties. I'll take 30%. Serious no BS.

I am open to anything in that area. No problems.

I've written plenty of DB's for all sorts of people. I'll have to do it in my spare time but what the hell. I'm getting married in Oz in November up at Cairns, i'll be going to Sydney on the way so i'll pop in and see you with it if you like.

Look forward to seeing you and hearing from you.

My contact details are

Mike McGuire
5 Burrell Steet
Beverly Hills NSW 2209
Ph (02) 95921507
Fax (02) 95920534
email Mike375@optusnet.com.au

What are your contact details?

Mike

 

[Mike375]

You've already been given examples and if you searched here for the PIM example db as suggested you could see for yourself how to approach your problems

I posted up how I do it and all you do is refer back to something else.

I did post up a challenge as to how others would do it. Can you do more that just refer to something. Are you able to do it?

You attack me but do not offer another way of doing it.

Mike

 

[___]

I posted up how I do it and all you do is refer back to something else.

The CORRECT way.

 

[Mike375]

Indeed.

In response to the original question, there is no average size - it depends on the purpose of the database. As Pat says, a minimum of one query per table.

AS for mike's, I get horrible images of it jumping up with Enter Parameter Value over 20 different queries each whenever a report or form is opened when one query can easily reference the parameter for all instances.

It may do the job for you mike but how much time do you spend maintaining it?

AS for mike's, I get horrible images of it jumping up with Enter Parameter Value over 20 different queries each whenever a report or form is opened when one query can easily reference the parameter for all instances.

Absolutely no way. All query paramenters are based on reference to form. If what you were suggesting was happening then it would be totally unsuitable for telemarketing.

But I am still waiting for how Pat Hartman and Ian are going to reduce the 15 diary display tables to one table....And where it works as required.

And then I am also waiting for Pat Hartmans "add one row" for the extra salesman. That is the most interesting one of all.

Mike

 

[Mile-O]

Never realised there was another 2 pages before I posted.

Interesting reading.

Personally, I'm not a professional developer/programmer/whatever. I've taught myself how to use over the past couple of years (although I must admit I've been able to code since I was 4).

When I build databases I make sure I do them properly and I like to think ahead to put some things in the users in my company would want in the near future.
___________________________________

Mike, how many of these people from within your company that have said it is good have had any sort of training with database design or have taken the time to look at the underlying design and structure to legitimatey comment upon it rather than pass mention on the overall aesthetic and operation of it?

I'm in my second insurance company (general the first time, medical now) and some of the stuff they've had prior to my ascendancy and arrival respectively over the two, there was an array of poorly designed databases. What made this all the more worrying was the fact that these databases had been designed and created by people external to the companies that they had brought in and paid a ludicrous charge to create something that looks like the Big Mac you get rather than the Big Mac in the photo that you expect. So, your comments on professional developers coming in and saying it is good really don't inspire and dull confidence - just keep it at a healthy neutrality. The majority of them are cowboys. Not Pat, though.

 

[Mile-O]

I'll make a one table diary system right now and post it for you to see.

 

[Rich]

I posted up how I do it and all you do is refer back to something else.

I did post up a challenge as to how others would do it. Can you do more that just refer to something. Are you able to do it?

You attack me but do not offer another way of doing it.

Mike

You need to lose the chip on your shoulder, nobody is attacking anybody!

 

[Mike375]

Mike, how many of these people from within your company that have said it is good have had any sort of training with database design or have taken the time to look at the underlying design and structure to legitimatey comment upon it rather than pass mention on the overall aesthetic and operation of it?

People that are "Developers" come and look and then see all the macros and then start thie spiel. The to help them a long a bit (I was a born stirrer) I show them the different tables that are obviously from the one family. The I get a non internet version of this thread. But at the end of the day I am an insurance salesman that runs a business that is telemarketing and insurance salesmen. Makes me a different person to the person who works for the insurance company. Not better, not worse but different.

I'm in my second insurance company (general the first time, medical now) and some of the stuff they've had prior to my ascendancy and arrival respectively over the two, there was an array of poorly designed databases. What made this all the more worrying was the fact that these databases had been designed and created by people external to the companies that they had brought in and paid a ludicrous charge to create something that looks like the Big Mac you get rather than the Big Mac in the photo that you expect.

I have often see similar with insurance companies in Australia. In most cases it does not matter because the insurance agents either decide to:

1) Not use it

or

2) Use something I make if they really need to use the computer.

Mike