Question for Starting Database

[ktilcu]

I have the great task of starting and managing a database of clients at my office. We have 2200 records of information in an excel spreadsheet with almost 23 columns of fields. Now my question is, would it be better to put all of this information on one table or to try and break it up into many tables with relationships.
F.Y.I. The titles for fields i have are: Last Name, First Name, CCDS (mailed, returned,update d), Tax Organizer (Paper, email, both or No), Tag type, Spouse First Name, Address, City, State, Zipcode, Birth Month, Birth Day, Birth Year, Spouse birth month, Spouse birth day, Spouse birth year, Email, daytime, Home #, Mobile #
The bolded ones are options that we filter by for mailings and phone lists and such. The CCDS field is one of the three options listed. The Tax Organizer field is one of the four options listed. And the tag Type field is one where we would classify clients as leads, Tax clients, investment clients or both. We eventually want to be able to break down the clients and add additional tags to sort by investment strategy, size of family, generation( baby boomer, gen X) and other additional things.
Please help if you can.

[Jim Doherty]

I have the great task of starting and managing a database of clients at my office. We have 2200 records of information in an excel spreadsheet with almost 23 columns of fields. Now my question is, would it be better to put all of this information on one table or to try and break it up into many tables with relationships.
F.Y.I. The titles for fields i have are: Last Name, First Name, CCDS (mailed, returned,update d), Tax Organizer (Paper, email, both or No), Tag type, Spouse First Name, Address, City, State, Zipcode, Birth Month, Birth Day, Birth Year, Spouse birth month, Spouse birth day, Spouse birth year, Email, daytime, Home #, Mobile #
The bolded ones are options that we filter by for mailings and phone lists and such. The CCDS field is one of the three options listed. The Tax Organizer field is one of the four options listed. And the tag Type field is one where we would classify clients as leads, Tax clients, investment clients or both. We eventually want to be able to break down the clients and add additional tags to sort by investment strategy, size of family, generation( baby boomer, gen X) and other additional things.
Please help if you can.

Hi and welcome to the scripts!

Your start point really needs to be an understanding of the principles of normalisation
Have a look at this thread on normalisation

http://www.thescripts.com/forum/thread585228.html

Once you get a feel for that then the structure of your database relies entirely on the design and the structure obviously dictated by you!

On looking at the principle of what you are doing and as a quickie piece of advice the central thrust of it is your client! so if you focus on that you will immediately begin to break it down into a natural and logical hierarchy or direction.

For instance a client can be either a person or it may indeed be a corporate body so a clients table consisting of the relevant field to suit you seems to me to be the obvious start point.

TableName tblClient
ClientID... (Autonumber used purely as a row reference and nothing more than that)
ClientType... (Personal or corporate)
PersonID...(a numeric value linking to a separate table storing individual people recorded only once table for instance called tblPeople)
CorpID......(a numeric value linking to a separate table storing individual corporate bodies recorded only once table for instance called tblCorporates)
InputDate... Default to Date() or Now() depending on your requirement
InputBy (default to Environ("UserNa me") to capture the login of your PC user to determine who placed record on system)
...and any other fields that are specific and relative to the individual client where it can be said that there exists the need to enter a single value in a single field only once.

Beyond that you are now entering into the area of recording potentially more than one item of data for an individual client for instance any number of individual clients might be located at a specific fixed physical address location the addresses of which might be individally stored only once in a table called tblAddress. The method of 'hooking these together' if you like is typically done using and intermediary third table to form a MANY to MANY relationship.

And then there are those other bits of nformation some of which never go beyond a few rows or so example a table storing people salutation Mr,Mrs,Ms,Profe ssor,Doctor,Rev erend and so on. These I tend to refer to as lookup values...used frequently and which need to be standardised in their own tables and linked to those separate table fields to which they relate. An example being tblPeople would contain a Salutation field in which would be populated values from the 'lookup' table if you wish to refer to it as that (its a table same as any other actually no difference) I personally distinquish tables from lookup using a tlkp preifx to the table names so that I know the table itself is in essence a 'lookup' table

Given you have just started on this take a serious look at table naming conventions Lechinsky/Roddick naming convention is a good standard and steer clear of putting spaces in field names! or object names!

At this point you could have information overload and me very sore fingers, so chew this over for a while and as you progress you will understand the concepts much better if not post back for clarification and experts and other contributors alike will be pleased to help. You simply need to decide for yourself the structure based on a few pointers here and there.

Hope this helps you get started and good luck

Regards

Jim :)