If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How many fields per table?
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? |
#2
|
|||
|
|||
How many fields per table?
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. -- Ken Snell MS ACCESS MVP "Access Africa" wrote in message ... 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? |
#3
|
|||
|
|||
How many fields per table?
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Access Africa" wrote in message ... 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? |
#4
|
|||
|
|||
How many fields per table?
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/conrad...essjunkie.html "Access Africa" wrote in message ... 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? |
#5
|
|||
|
|||
How many fields per table?
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 "Jeff Boyce" wrote: 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Access Africa" wrote in message ... 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? |
#6
|
|||
|
|||
How many fields per table?
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 |
#7
|
|||
|
|||
How many fields per table?
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. |
#8
|
|||
|
|||
How many fields per table?
=?Utf-8?B?QWNjZXNzIEFmcmljYQ==?=
wrote in news 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 |
#9
|
|||
|
|||
How many fields per table?
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 |
#10
|
|||
|
|||
How many fields per table?
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. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table problem | Redwood | Database Design | 29 | April 3rd, 2006 04:58 PM |
Table design problem? | Melissa | Database Design | 29 | November 18th, 2005 04:14 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Complicated Databse w/many relationships | Sรธren | Database Design | 7 | July 13th, 2004 05:41 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |