Designing a database for a windows application

[mpsoutine]

Hi,

I have a guestion about designing a Access database that will be used by a windows program. In my case I'm designing a database that will have information about the loans of a bank has: name and address of customer, start date and maturity date of the loan, amount and interest rate.

My question is what type of field description should I use in the database. My first choice is to use the Text field description. I don't plan on doing any calculations with the numbers.

The users of the application will be moving from edit control to edit control typing in the information.

If I'm just entering text is there any advantage to having the type of information I'm entering match the database field in some way. Example. Should my interest rate be a float and the loan amount be a long?

Soutine

 

[BiggerBrother]

although you do not necessarily want to perform any operations at the moment, you may find in the future you just want to find all the clients who have more than a certain amount in their acount etc. For this reason, you should try and match the type of input to the field type. It also ensures the data is uniform, in the instance of dates etc.

Also, if you have more than 1 table, and you are linking using client ID ref or similar, these should be the same data type, otherwise relationships will produce errors.

I would certainly use float, and long data types, as they use less memory than 50 place text strings. Thus this will make the actual size of your database file smaller.

Hope this helps

BB

 

[tahilg]

I would recommend that you use whatever is the appropriate format for the data type. You can hold very large numbers in two, four or eight bytes that would require many more bytes as text characters.

Access stores date AND time internally as a four byte field. A normal date would require anywhere from six to ten bytes as text characters.

For the interest rate I recommend you set Field Size to Single (Number) which is four bytes. Normally interest rates are formatted and used as nn.nn which would require five bytes as text. You could store it without the decimal point as just four text bytes but that would make your programming more complex even for simply displaying it.

HTH

Gunny

 

[psemianonymous]

For any *currency* transactions, I'd highly recommend you use the *Currency* data type in Access. Other DBMS's have their own equivalent data type--I think you can define long values with a certain number of decimal places built-in.

You will need the Currency type to hold precise information. Floats (and yes, even doubles) can suffer from precision problems because they do not consistently store the data up to the #.## level if the number is very large. Currency values will always keep their precision.

Text values could possibly hold the precision as well as a currency value, but how are you going to sum the values? Or sort? Or ... something I haven't even thought of off the top of my head?


Text sorting:
&quot;11.10&quot; < &quot;2.00&quot;

 

[ArkM]

I think text type must be the last choice.
It's (almost) impossible to build useful, efficient and complete queries if you allow users enter (arbitrary) data in a text field. It's (almost) impossible to built in complete incoming control over all text fields content.
Set aside text type to store true texts or (short) enumerations only (m/f).
Otherwise your DataBase will be turned into the Great Illusory DustBin - sooner or later.