How many fields per table?

[Access Africa]

I am busy with my database at the moment and where I initially thought
that I will have 1 Table for e.g all Personal Information I am
realizing that if I do it that way I will end up with a table with
well over a hundered fields in it.
Logically I am thinking that this will be to much so I am now looking
at breaking it up in to different tables, how may fields is a
reasonbable amount of fields to have in one table?

 

[Ken Snell \(MVP\)]

Reasonable is defined by the database's design and purpose, but it's rare
(and may be an indication of unnormalized database structure) if you have
more than 30 - 35 fields in a table. From what other ACCESS MVPs have
posted, and from mypersonal experience, that seems to be a pretty good
number -- most tables will have 2 - 15 fields.

 

[Jeff Boyce]

From your tag line, you may be the same person who has posted earlier
questions and been advised to look into normalization as an excellent
starting place before building tables in Access (or in any other relational
database). From your question, I suspect you still need a bit more time
looking into the topic.

There is no "right" answer for how many fields are in a table, as this is
driven by the normalization process. However, a rough rule of thumb is that
a well-normalized data structure will only rarely require a table with more
than 20 or 30 fields (and even this many is unusual).

Have you defined the entities and relationships? When you say "Personal
Information" and "well over 100 fields", I don't have a clue ... can you
provide some specific examples? It would make it easier to offer some
specific suggestions.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Lynn Trapp]

I'm not sure how you would define "reasonable," but I have seldom built a
table with more than 25 or 30 fields. Any time you get any larger than that
you are most likely suffering from a pretty severe design problem. However,
you should not concern yourself first with the appropriate number of fields
but with whether or not all the fields in your table are attributes on one
subject. If they are, then they can be legitimate fields in the table. You
should also make sure that you have no repeating fields in your table --
i.e. Child1, Child2, Child3, etc.

You would do yourself a good favor to get a copy of "Database Design for
Mere Mortals" by Michael Hernandez.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Access Africa]

Hi
I have been reading up on normalization and now I need to start planning out
all my tables and fields.

Please bear with me I know that I can be tedious but I really need to get
this done.

What I need to do is
Have an item in my switchboard called Business info
In this section I need to have the following fields:
ô€¹ï€ Is the application in respect of
- a new business?
- an existing business?
- a franchise?
- a newly acquired business?
ô€¹ï€ If a franchise, enclose the franchise agreement
- Name of the business
- Telephone and facsimile numbers
- Trading name of business
ô€¹ï€ If a company or close corporation, state the full registered name and
enclose Form CK1 or CK2 where applicable
- Street address from which business will be / is operating
- Postal address of business
ô€¹ï€ If a company or close corporation, the address of the registered office
ô€¹ï€ Registration number
ô€¹ï€ Date registered
ô€¹ï€ Owners of the business:
- Name(s)
- Director, partner, member or shareholder
- Percentage holding
- Financial contribution
The personal details section should be completed individually by each of the
owners in the business.
ô€¹ï€ First name(s) and surname
ô€¹ï€ Date of birth
ô€¹ï€ Identity number
ô€¹ï€ Business and home telephone numbers
ô€¹ï€ Full residential address
ô€¹ï€ Nationality
ô€¹ï€ If not a South African, are you a permanent or temporary resident?
ô€¹ï€ How long have you been resident at your present address?
ô€¹ï€ Is your residential property owned or rented?

Marriage details
ô€¹ï€ Married, single or divorced
ô€¹ï€ Married by ANC or COP
ô€¹ï€ Has your status changed since 1984? (If so, attach a copy of the
contract).
ô€¹ï€ If married by ANC, attach a copy of the contract
ô€¹ï€ If divorced, on what date was it final? (Attach a copy of the final
decree of divorce).
ô€¹ï€ Number of dependants, including spouse
ô€¹ï€ Number of children and their ages

Spouse’s details
ô€¹ï€ First name(s) and surname
ô€¹ï€ Date of birth
ô€¹ï€ Identity number
ô€¹ï€ Employer, current position at work and work address
ô€¹ï€ Business and home telephone numbers
ô€¹ï€ Annual income

Personal references
ô€¹ï€ Names, addresses and contact telephone numbers of at least three people
to whom reference can be made.

Business Plan Guidelines 6
Employment history
ô€¹ï€ Highest educational qualification obtained and when achieved
ô€¹ï€ Formal apprenticeships or pupilage and when completed
ô€¹ï€ Career history details: employer, employment period, type of work, last
position held, annual income
ô€¹ï€ In what capacity will you be employed in this business?
ô€¹ï€ The monthly income you will earn from the business
ô€¹ï€ List the other financial benefits that you will receive from the
business and give Rand value.

Financial affairs
ô€¹ï€ Do you have or have you had an interest in another business? Give details
ô€¹ï€ If you have been sequestrated, when were you rehabilitated?
ô€¹ï€ Give details if you were found guilty of criminal offences
ô€¹ï€ Give details of judgements for debt against you

Details of personal assets
Fixed property
ô€¹ï€ Stand number, street address, name in which registered, market value,
municipal value.
ô€¹ï€ Provide details of these properties sold under Deed of Sale

Machinery, vehicles and equipment, etc
ô€¹ï€ Registered owner, description, year model, date purchased, registration
number, leased or owned

Furniture and fittings
ô€¹ï€ Estimated market value
ô€¹ï€ Ownership vested in yourself and your spouse?

Life insurance
ô€¹ï€ Life insured, insurance company, policy number, annual premiums, death
value, date taken out, type of policy, surrender value, beneficiary

Banking accounts
ô€¹ï€ Account in the name of, name of bank, branch name, type of account,
account number, present balance

Investments, fixed deposits, etc
ô€¹ï€ Investment in the name of, where invested, type of investment, amount
invested, interest rate, expiry date

Share investments in listed companies
ô€¹ï€ Investment in the name of, name of company, number of shares, present
market value

Business Plan Guidelines 7
Share investments in private companies
ô€¹ï€ Investment in the name of, name of company, number of shares, present
market value, copy of the latest financial accounts

Membership of close corporation
ô€¹ï€ Name of member, name of close corporation, percentage membership, amount
contributed, copy of the latest financial accounts

Details of personal liabilities
Fixed properties (listed above)
ô€¹ï€ Stand number, bonded to, Rand value of bond registered, outstanding
balance, monthly repayments

Machinery, vehicles and equipment (listed above)
ô€¹ï€ Registered owner, registration number, financed by, outstanding balance,
monthly repayments

Furniture and fittings (listed above)
ô€¹ï€ Items not yet fully paid for, outstanding balance(s), monthly repayments

Jewellery, cameras etc. (listed above)
ô€¹ï€ Items not yet fully paid for, outstanding balance(s), monthly repayments

Life insurance policies ceded to a third party (as listed above)
ô€¹ï€ Policy number, ceded to, why ceded

Credit cards
ô€¹ï€ Name of card holder, name of bank, present balance, expiry date,
ordinary limit, budget limit

Banking accounts (listed above)
ô€¹ï€ On which account number(s) do you or your spouse use overdraft
facilities?
ô€¹ï€ What are the limits? What are the expiry dates? What are the present
balances? Provide details of security held by the bank

Short and long term-term personal liabilities, excluding normal trade
creditors
ô€¹ï€ Owing by, owing to, outstanding balance(s), how or when payable, monthly
repayments

Suretyships
ô€¹ï€ In favour of whom have you signed sureties? State amount of the
sureties, purpose of suretyships

Business Plan Guidelines 8
Notarial bonds
ô€¹ï€ Over which of your assets have Notarial bonds been registered? In favour
of whom? For what amount? For what purpose?

Income
ô€¹ï€ The gross monthly income of each spouse

ô€¹ï€ If buying an existing business : Why the current owner wishes to sell
the business, details on the current owner and selling price of the business
ô€¹ï€ Describe the present or proposed activities of the business
ô€¹ï€ If the business is part of a group of companies, provide details in the
form of an organogram
Business Plan Guidelines 9
ô€¹ï€ For a company
- Authorised share capital
- Issued share capital (number of shares)
ô€¹ï€ Give details of owners listed above who have an interest/interests in
other businesses
ô€¹ï€ How long has the business been owned by the owners listed above?
ô€¹ï€ Provide details if the business or any of the listed owners have ever
been compromised with their creditors

 

[Access Africa]

Okay after a lot of head scratching and reading till my eyes bleed I came up
with this:
http://www.webassist.co.za/bpplanning.html
Please have a look and let me know if I am on the right track now?
Regarding setting out the tables and fields and normalization
Regards
Natasja Atherton

 

[mnature]

You just seem to keep creating spreadsheets.

To dissect this part of your table:

ô€¹ï€ Is the application in respect of
- a new business?
- an existing business?
- a franchise?
- a newly acquired business?

These are all fields? That is not how to normalize a database. To
normalize means something like this:

tbl_Questions
QuestionID (PK)
QuestionText (the question is placed in this field, it isn't a separate field)

Now, notice that the questions are completely separate from the answers.
That is what is meant by normalization. Next, you build a table that lists
the different people you ask these questions of. Then you build a table that
joins the questions, the people, and the answers.

tbl_People
PeopleID (PK)

tbl_Answers
AnswerID (PK)
QuestionID
PeopleID
AnswerText

Using this format, you can have hundreds or thousands of questions, because
your tables are normalized.

 

[Tim Ferguson]

Have an item in my switchboard called Business info
In this section I need to have the following fields:

From what follows, I would guess at least as many tables as this:

Applications
Businesses
Franchises
Offices
People
Ownerships
Nationalities
Marriages
BusinessPlans
Employments
Properties
PhysicalAssets
InsurancePolicies
BankAccounts
ShareInvestments
CreditCards
Owners

.... but then again, I have no knowledge of your business environment.
Just how all these entities relate to each other, and what attributes
each of them have, is entirely up to you of course.

Do remember that the place to start is modelling the real-world stuff;
then move onto paper forms and data collection. If you try to do it the
other way round, you'll find yourself hopelessly painted into a corner.

Hope that helps


Tim F

 

[Access Africa]

Like this:
tbl_Personal Assets
PK:Asset ID
Owner_ID
Typeofasset_ Look-up Text
DescriptionofassetText
Purchase Date Date/time
Purchase Amount Number
Present Market Value Number

What I tried to do with the diagram was just to put all the information in
some kind of order so that I can start with the normalization.
Thank You for your input
Natasja Atherton

 

[mnature]

Like this:

tbl_Personal Assets
PK:Asset ID
Owner_ID
Typeofasset_ Look-up Text
DescriptionofassetText
Purchase Date Date/time
Purchase Amount Number
Present Market Value Number

It looks like you are getting closer to normalization. Instead of
"Typeofasset_Look-up Text" I would suggest:

tbl_AssetTypes
AssetTypeID
AssetText
AssetInfo (just an etc. field to indicate any other information you want to
use for a particular asset type)

Quite often when you have some repeating data, you can make that into a
separate table, with its own primary key to refer to. It is a nice way to
organize data, making it easy to add additional data if needed, plus being
able to more fully describe or characterize that data within the separate
table.

You might even find that an asset type can be further qualified (such as
capital asset, real estate asset, etc.), and you can do this:

tbl_AssetTypes
AssetTypeID
AssetDesignationID
AssetText
AssetInfo

tbl_AssetDesignations
AssetDesignationID
DesignationText
DesignationInfo

That isn't to say that you should do this, but it illustrates how you can
have nested data. Try not to get too carried away, because at some point you
will want to fill in this data.

 

[Access Africa]

Looking at what you siad here I was hoping you can just clarify this for me:

I have a lot of areas that are just questions and answers (long text answers)
e.g:
Under Premises:
Describe the location of the business, the size of the premises are the
premises leased or owned

Can I use this
tbl_Questions
QuestionID (PK)
QuestionText (the question is placed in this field, it isn't a separate field)
Assuming that the QuestionText will be a set question that I will put in?
And then I will have a seperate answers table?

I am not that good with this yet but if you have any questions on PHP I can
help. I feel so stupid today.

Regards
Natasja

 

[mnature]

tbl_Questions
QuestionID (PK)
QuestionTypeID [use this to designate what kind of question it is, such as
premises]
QuestionText [the question is placed in this field]

QuestionTypes
QuestionTypeID (PK)
QuestionTypeText

tbl_People
PeopleID (PK)
PeopleName

tbl_Answers
AnswerID (PK)
QuestionID [ties back to a particular question]
PeopleID [ties back to a particular person]
AnswerMemo [Make this a memo field, and you will have lots of room for long
answers]

Under Premises:
Describe the location of the business, the size of the premises, are the
premises leased or owned

These are three different questions, each would belong to QuestionType :
Premises, and would relate to the People and Answers tables. Once you have
set up the questions, then you can have as many answers as you want, with any
particular answer always related to a particular person and question. You
could even have a date field in the answer table, and have the same question
asked several times over a period of time, to see how the answer changes.

The QuestionType field would allow you to sort the questions/answers
according to what they refer to.

 

[Access Africa]

MNATURE:
Looking at what you siad here I was hoping you can just clarify this for me:

I have a lot of areas that are just questions and answers (long text answers)
e.g:
Under Premises:
Describe the location of the business, the size of the premises are the
premises leased or owned

Can I use this
tbl_Questions
QuestionID (PK)
QuestionText (the question is placed in this field, it isn't a separate field)
Assuming that the QuestionText will be a set question that I will put in?
And then I will have a seperate answers table?



I am not that good with this yet but if you have any questions on PHP I can
help. I feel so stupid today.

Regards
Natasja

 

[Tim Ferguson]

if you have any questions on PHP I can
help.

Good MySQL design is exactly the same as good Access design...


Tim F