Help With Table Design/normalization
Greetings-
I am having trouble with the table design of my db.:confused:
What I am is a human resource supervisor trying to develope a database to
track the hundreds of actions that come in and out if my office on a weekly basis.
I am almost there, in regards to table setup and relationship setup, except I am stuck on something and any advive I can get would help tremendously.
Here is what is going on:
I need to be able to relate my customers to the actions they turn in..and track them, the actions, until complete.
I have attached my db and will answer any questions needed. Will someone please take a look at it and evaluate what you may think is needed to get the "ball" rolling.
Thank you for your help
DW
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
My Questionaire Table Design-normalization! Your Thoughts Please?
Im planning a db client's dinner questionaire that will comprise of client's favorites (meals, games, transportation). Each client is allowed to bring a guest. So the client's will also be filling out "the same set" of questionaire for his/her guest. Meaning, a user will need to fill out the same form twice, one for himself and one for his guest. Now, my question is, should i have two tables. Client's questionaire table and Guest questionaire tables with foreign key in the guest's table showing who's guest the question belongs to? Or should i have one table for everyone (since they are the same set of questions). Having one table seem like a wise idea since it's the same set of questions (don't want to have 2 duplicate tables with only one field different). Now, if i go with one table. How do i go about handling this? So that client's and guest's info are linked. and i can run a query to tell who's guest it is? I have never tried thsi before. Care to share your thoughts?
View Replies !
View Related
Design Help For Normalization
Hello all, I have been tasked to create a database to track tooling information for my company. I have a solid idea on the process involved and would like some help getting my tables and relationships setup with normalization. I have noticed in the short time I have been learning access that normalization is the key to happiness. Anyway here is what I have: tblModel *ModelID ModelYear ModelCustomer ModelStartDate tblParts *PartNumber ModelID PartDescription PartProcess tblTools *ToolNumber ModelID ToolSource ToolComments ToolComplete tblProjects *ProjectID ModelID ToolQtyNeeded PartQtyNeeded A short synopsis on how this should work: A model is built from many different parts, these parts are sometimes (usually not always) associated with the tool that is used to build them. A tool could be used to make several different parts and some parts use several tools. In addition to this, a model can use a different quantity of parts to build it and those parts can require different quantities of tools to produce them. I have tried several different ways to relate these tables together, and would like to ask the braintrust that is this forums for any help they could give me. Thanks again
View Replies !
View Related
Normalization/table Set Up
I'm not a programmer and know very little about setting up access databases other than the very basics...however...due to my politics background, I'm setting them up for the local government....just my little disclaimer about why I'm asking stupid questions. I am creating a database for multiple users who are of two sorts. The users deal with either the setting up of a contract, or with the payment of a contract. Both of these groups need to be able to access different information about the same contracts. What seems most logical to me is to create two tables in the same database, one for the contract, and one for the payment information and have both update when one or the other is worked on. My basic set up for now is this: Table One: contract information Table Two: Payment information Link Key: Serial Number from contracts These are my questions: 1) is there a way to set this up so that the only way someone from the payments group can create a linked record is if a record with the same serial number already exists in the contracts table? 2)Is there a way I can set it up so that when someone in the payments group starts to input their information, they can find the serial number they want, and have certain fields show up? For example: I want them to be able to search for a serial number and have the contract name show up with all of the previously entered payment information, then have a new field each time they go into it for their next payment (if that makes sense). 3) When linking, hopefully in such a way as to do the above, do I need to have the same field name in both tables, or...? I'm a bit confused as to how this works as initially i created a field in both tables that would have the same serial number and tried to link those..and it didn't work. Sorry if this is so confused...I'm sort of learning as I go along.
View Replies !
View Related
Table/Normalization Help Please
Hey buddays, I have a contract-tracking database that has been in use for about a year. It used to be that every contract had the same number of periodical "applications" with it - the companies we worked with would send in 4 applications over time. So, I had this set up: tblCompanies (the companies with whom we work) - CompanyID (Primary Key autonumber) - CompanyName - Address, contact info, etc. tblContracts (the contracts we issue) - ContractID (Primary Key autonumber) - CompanyID (foreign Key) - DateIssued tblApplications (things companies send back to us over time) - AppID (Primary Key autonumber) - AppDesc (description of the application - something like "Application 1", "Application 2" , etc. tblAppsRecvd (Join table/log of apps received) - ContractID (foreign key to tblContracts, so we know which contract this applies to) (also a composite primary key, so we don't duplicate an app to a contract) - AppID (foreign key to tblApplications, so we know which application we rcvd) (also a composite primary key, so we don't duplicate an app to a contract) - DateRcvd Pretty self explanatory. Everything was keen, and I was reaping the rewards of a job well done. But now we've expanded out contract offerings. He have introduced 2 new types of contracts, and each contract has a different amount of applications associated with it. Contract 1 has 4 apps. Contract 2 has 2 apps. Contract 3 can have 1 to 8 apps. (The last app before a contract closure is always considered "Final" - so if we get 5 apps, it would be apps 1, 2, 3, 4, and Final.) I've tried implementing a couple different schemes, but none seem to be fitting the bill. The type of contract will dictate how many apps we receive - i.e. If we are using Contract 1, then we KNOW we'll recieve 4 apps over time. So, is this as simple as adding a "ContractTypeID" to the tblContracts with an appropriate tblContractType table, and expanding tblApplication to more apps, or is there a smarter scheme? Thanks, buddays!
View Replies !
View Related
Table Normalization
Anyone can direct me to place the following fields into different tables. I currently import all this info from an Excel spreadsheet. Here are the fields: Record No., datarange, picker-nbr, picker-name, tt-picker-piece, cust-pieces, invc-pieces, invc-date, group, load, batch, pieces, item, description, slot, reason, cust-nbr, and invc-nbr. Thank you Patriot76
View Replies !
View Related
Need Help With Table Normalization
Im setting up a database that contains information about printed circuit boards that are being developed for our current project. The information that i need includes the following: Board # - 3 digit number Board Name - Module # - 9-digit number Module Name - Project Leader - engineer responsible for design In/Out/Oem - inhouse, outsourced, or purchased OEM Designer - engineer or outsource contractor who designed the board MFG/Assembler - company responsible for building the boards Directory - file on network drive the drawings are located in There can be mutiple boards in a single module. So i have 3 tables right now. pcb_tbl - this includes all the info above minus the module info. Module_tbl - this table includes the module number and name and responsible engineer, with the module number being the key. module_pcb_tbl - this table lists the module number in the first field linked to the module table, and the pcb number in the second field linked to the PCB table. Am i doing this right?
View Replies !
View Related
Stumped. Table Relationship / Normalization
I am having trouble visualizing how to break out the following info into functional Tables I have a table to hold Items tblListG1Items IDItemList ITemNumber ItemName I then have a detail table tblDetailG1Items IDItemDetail FKToItemList ItemMFG ItemMSP ItemLead Here is where I get lost. Each item has a variable for each month So item 1 January = .01 February = .03 ECT. Once set the Variables rarely change, at most once per year. I will need to be able to pull an item and based on what month it is pulled uses the variable to multiply several other factures. My first Thought was, tblVarItemList IDVarList FKToItemList VarJanF VarFebF VarMarF --And so on-- It just does not look right. Any Suggestions on what might work Better. :confused:
View Replies !
View Related
Normalization: One-to-many Out Of A Junction Table Without A Primary Key
Hello, I have attached a screenshot of the relationships in (a section of) a database I am working on. Would anyone please be willing to view it and offer any suggestions as to whether is fully normalized or not? Here is the description: An account [table, Account] consists of some money. That money is split-up and invested into several different places. The funds that are available as choices are in Fund; the funds that are chosen are in InvestmentInstance. Different, unaffiliated accounts can invest in the same fund. I would like to keep track of when an account begins to invest in a fund and when it stops investing there (“InceptionDate” and “CloseDate”). The generic, account-independent performance of a fund is kept in Price, where the values come from Yahoo! Finance or something like that. Similar to Price, MarketValue stores the dollar-amount of an investment, specific to an account. I think I have most of it set OK, but my main concern is about the relationship between InvestmentInstance and MarketValue. As the picture shows, I use InvestmentInstance’s “Id”* as the “one” in the one-to-many relationship, but “Id” is not a primary key. To get it to work, it was necessary to set the Index option to, “Yes (No Duplicates),” so that it was a unique index. Is this a bad practice? Should I just include “Id” as part of the compound primary key? I didn’t think making it part of the compound key was a good idea because then it would defeat the purpose of having “FundId” and “AccountId” control the uniqueness of records. Am I missing something important? In other words, how can I correctly create a one-to-many relationship that begins in a juntion table (InvestmentInstance) and connects to a new table that was not involved in the normalized many-to-many relationship (MarketValue)? Thank you! * I know many like to include the table name in the ID, like, “InvestmentInstanceId,” and it is only my preference to keep it just as “Id” for a local key for simplicity, but add the table name when it becomes a foreign key. I feel “Id” is good enough for my purposes and it’s table location is usually clear from the context.
View Replies !
View Related
Help With Table Design
I am having problems coming up with a Table design and need help. I am trying to design a table that will have 8 categories, 47 subcategories, and then choices within those categories. Much like the following (this is only one category example, there will be many): (Main category) Floors -> (Sub categories) Joist, Carpet, Vinyl Flooring, Wood Laminate -> (Choices for Joist Sub Category) Structure Wood 2X8 Joist, Plywood 2X8 Joist, Structure Wood 2X10 Joist, Plywood 2X10 Joist This only shows the flow if the main category is "Floors", sub category "Joist", and then the choices under subcategory "Joist". There will be others for the other categories, subcategories, and choices. Can someone help me or steer me in the right direction in setting up the database table/tables to accomplish this. Oh and BTW, there will be prices attached to each "Choice" under the subcategories. I hope that this makes sense... any help would be much appreciated.
View Replies !
View Related
Table Design
if i had a table with products and i want to keep a record of what has been chaged or added to this product, whilst keeping the old records how could i set it up in a databse? the excel spread sheet looks like this product commments 1 chnged this and that 20/08 1 modifyed component 21/08 2 newest model 3/08
View Replies !
View Related
Table Design Help
Hi all, i wonder if anyone can help? It's a simple question really. I'm currently learning databases and am doing a project, just to show that i understand everthing i've learnt so far. I have to produce a small database. There's nothing difficult about the project, and its a very basic database, but i was just hoping to ask a small question. I'm producing a DVD database, to hold details on a collection. My main table holds the majority of the fields, as follows; lngDVD_ID strDVDTitle strYearReleased lngDiscAmount dtmFeatureLength lngCertificationID ysnBoxset ysnExtraFeatures strDirector lngMovieStudioID lngCategoryID curPurchasePrice The problem came with boxsets. Originally i was just going to have boxsets as one entry, but some contain a few dvd's, each with different certification, etc, and i wouldn't be able to record running length, director, etc. So i added a ysnBoxset field (just to record whether a dvd was part of a boxset or not) and then made a new table with lngDVD_ID strBoxsetName So now i can record which are part of a boxset, and the name of the boxset they belong to. But.. then i got to the problem of Purchase Price. Obviously i cannot record an individual purchase price for those which belong to a boxset, and i want to be able to record the price of the boxset. So my question is... is it acceptable to add a curBoxsetPrice field to tblBoxsets, which would allow me to store the price... and then just leave curPurchasePrice as £0.00 for those dvd's in a boxset??? Or is there a better way of doing it? (I know there is, but i'm a novice :( ) Thanks for any help you can give, sorry for the lengthy explanation! :rolleyes:
View Replies !
View Related
Table Design ??
Within my db I have a table, tblProjects which currently has 36 fields. Each project will have a fixed set of 12 tasks, each having a begin and end date as well as a yes/no "completed" field. This would add another 36 fields to tblProjects. Instead, I have created a new table tblTasks which has these 36 fields along with a PK(TaskID) and FK(ProjectID) and is related to tblProjects. I believe tblProjects is already normalized properly with relationships to tblCompanies and tblContacts as well. I'm not sure if this was neccessary, or should I have just added the task fields to tblProjects. What, if anything, are the advantages of one way over the other? I'll be upsizing to SQL Server, does that make a difference? I appreciate any advice, Sup
View Replies !
View Related
Table Design
Hi, Bit of a long one but I require some advise if people can supply it. I've started creating a DB that is now getting out of hand due to me being inexperienced. The things I want to do now are getting way to complicated. From reading various posts I know that this is all being caused by my database design and while the DB is still young I'm go back to square one. I want your advice on this if you can to ensure 3 weeks from know I'm again not chasing up weird bits of VB code to make it all work. I basically have a lot of information to hold that interconnects with each other. I'm making up a role profile DB (so we can assign define access rights to a computer system) I have lots of info to collate but the basic stuff is. Staff Records (Name, business area & ID) Applications (What computer applications do staff require) Shared Drives (What access to shared drives and folders do they need) Shared Mailboxes (what access to shared mailboxes) Roles (users will be assigned roles to suit their requirements) Basically what I have at the moment is below. Roles Role ID Role Name and about 15 other fields that dont really matter for this Applications Application ID Application Shared Drives Shared Drive ID Shared Drive Shared Mailbox Mailbox ID Mailbox Each role will only have a select number of applications, mailboxes and drives from the overall list. so role 1 may give access to 2 of the 10 applications, 1 out of 5 shared drives and 3 out of 4 mailboxes. Role 2 on the other hand may need 9 out of the 10 applications, 1 out of the 5 shared drives and 1 out of 4 of the shared mailboxes. I just don't understand how I can create a form from these tables that would display all the different roles with their respective apps, mailboxes and drives they would need. any advice you can give to help whould be appreciated. Again sorry for the long post.
View Replies !
View Related
Table Design
I am designing a db for our Law office. I want to have tables for different types of Civil Cases-ie Divorces, Deeds, Contracts, Personal Injury, Auto Accident, Slip and Fall, Product Liability, etc. The divorce, Deeds, tables are easy but I am having difficulty deciding on the personal injury tables. Heres my dilemna. Can I get away with one or two Personal Injury tables even though there will be blank data fields. For example, I will need a field for the name of the product in a Producrs Liability field. This field will be blank in all other Personal Injury Cases. I know the rule is you don't do that but my alternative is 15-20 tables for every concievable Personal Injury Case- Auto Accidents, Slip and Falls, Fraud, Prouduct Liability, Accounting, Attorney, Dental and Medical Malpractice, etc. This is in addition to other civil case tables, criminal case tables and federal case tables. Anyone have a suggestion?
View Replies !
View Related
Table Design Help
I am not sure if which I should be using as the main entity for my tables. I am creating a database for hourly information on individual employees. Each employee will have the same types of hours to keep track of (weekly, bi-weekly, monthly etc) I naturally thought to treat each employee as the databases own entity and created fields for each type of hours but hit some trouble when trying to pull queries to compare each employees hourly information against each other to see who is being efficient or not. Each employee has the same exact fields but with just different numbers. We track each type of hour by week (Field One: Week 36, Field Two: Aug 28-Sep 03)This is the first time I am creating a database from scratch. All advice greatly appreciated and TIA :confused:
View Replies !
View Related
Table Design
I have a table within a database and i was wondering whether or not i should split the table into 2 seperate tables. The layout of the existing table is to record quotation information ie product codes, cost price, sell price, delivery dates available etc and it also contains fields that allow the user to state if the quote has been authorised or declined, when it was authorised and a number of options to explain the reason the quote wasn't accepted. QuotationID - Primary Key EmployeeID - Foreign Key CustomerID - Foreign Key EnquiryID - Foreign Key ProductCode Quantity CostPrice SellPrice DeliveryDateAvailable Notes Authorised - Yes/No field Type DateAuthorised Declined - Yes/No field Type ReasonDeclined LostPrice - Yes/No field Type LostStock - Yes/No field Type LostDelivery - Yes/No field Type LostCustomerClient - Yes/No field Type LostNoFeedback - Yes/No field Type above is the layout of the table, does anyone think that it might be a good idea to split this table into two tables, one table to record the quote information and another to hold the authorisation or decline information??????
View Replies !
View Related
Table Design Help
i am creating a database for a local football club. I want to show player information (the main focus of the database), such as, what guardians are responsible for this player, what are the Doctors details for this player, what team does the player play for etc. I have seperate tables for the Guardian, Doctor and Team information. I also want to hold Staff details, (Name, address, staff title etc) My problem is that someone in the staff table, could also be in the guardian table. Should i combine the Guardian and Staff tables into one table as I dont want to have to enter the data twice for both a staff memger and a guardian Note: a player can have more than one guardian a guardian can be responsible for more than one player. What is the best way to approach the design of these database tables. Thanks in advance Pat
View Replies !
View Related
Table Design Help..........I Think
Have a database that I am working on this is constructed to perform Compliance tracking of wellness activity by employees. Have all my tables, queries and a form that has 3 tab controls as well as a subform on one of the tabs. It is necessary to create a quarterly report card based on 6 wellness cateogories that have specific criteria. I thought I was OK but now I am having difficulty pulling it all together on the report. I now think that it has to do with my table construction and I am looking for advice. If there is someone I could send my DB to who can take a look and give advise or assistance I would greatly appreciate it. Thanks.:confused:
View Replies !
View Related
Help With Table Design
Hi, I'm currently in an internship working solely with databases. I've become really interested in designing databases and I now want to design my own. I was wondering if I explained my table structure then someone could give me their thoughts and ideas for improvement. I have a huge sport trading card collection and I wanted to create a database for it. This way I could run some quick queries to find out how much my collection could be worth, how many cards I have, easily keep track of duplicates. Things like that.. This might seem strange, but I think it would be a good learning experience that would help me utilize many of the features of Access. My Table Structure: Ok I have a Company table(Topps, UpperDeck, etc) and they sell many boxsets(which would be another table).. Boxsets have many Cards(which would be my third table)... Now here is where I get confused. I want have a sports table that would categorize my cards by what sport they are, but I don't know where I could link this. I also have a players table. This table will have a 1:M with Cards because 1 player can have many cards, but couldn't Cards and Players have a 1:1 because 1 card shows one player??? Finally, I want a Price Table that will be linked somewhere, but I've yet to figure out where to link this too.. Have I missed anything??? Your ideas and opinions are greatly appreciated!!! Thanks!
View Replies !
View Related
Table Design
i have a table(patients) with more than 1500 records.this table contains patients information like diagnosis etc.I use cbo boxes in my form to choose info.(i have separate tables to keep this information e.g.table for diagnosis, table for treatment procedures etc. of course these info are kept in the patients table as text fields.and also i have many query with criteria to select specific records(for example ) parameter value is [Enter diagnosis] and i type for example "cardiac" to get the list of cardiac patients etc. now , the size has become large . so i want to do somthing to solve this i designed this when i was very new to access. how if i want to change the field type from text to number which can look up the required info from other table. but if i do this , i think that my queries want work . any advice on this. i have about ten fields to be changed from text to number.
View Replies !
View Related
Something Like 3D Design Table?????
In this database the Tables are: Materials (Material_ID, Material_Name, etc.) Users (User_ID, User_Name, Date_modified, Hour, etc.) Enviromental Values Of Materials (Ev_ID,Cretarion_01, Cretarion_02, etc.) So i want to build a relation like a 3d axis system like that: X: Materials Y: Envirometals Values Of Materials Z: Users So for each user may correspond with 1 or all of the materials and for each material many enviromental values(cretarion_01,cretarion_02, etc.) Namely i want to do a database where a person saves a number of material (1 or all) and for each material a number of Enviromental Values(1 or more). I make a relation (see attach file):( :confused: What is your opinion about that? It would be a broblem if the PK's type is text? Thanx in advance.... Happy new year:)
View Replies !
View Related
Table Design
Being a newbie I have a general table design question which I would be grateful if someone could advise upon. In Access 2000 I currently have 3 tables for use in a holiday cottage rental system: tblCustomer (customer details) name (text) address (text) telephone (text) etc..... customerID (autonum) tblreservation (cottage reservation details) cottage (text) - linked to tblCottage customer (test) - linked to tblCustomer start date (date) number of nights (number) Adults (number) Price (currency) etc..... ReservationID (autonum) tblCottage (list of cottages available for rental) cottage (text) CottageID (autonum) I need to add a table structure which defines the price band for each cottage at several times throughout the year, in order to take account of peak holiday periods etc. The price field in the tblReservation table needs to refer to the correct price band based on the cottage and the start date of the reservation. Any thoughts how best to approach this would be greatly appreciated. Many thanks Chris
View Replies !
View Related
Table Design
Hello, I am looking at creating a database for a charity, to help them maintain there volunteer information. The database will need to hold information about the charity, volunteers and there roles, language and status. This is the db design so far. Branch id PK - charity number name * house * street1 * street2 street3 town * city postcode * p_office * p_land * p_mobile fax email not sure of data type and length www not sure of data type and length volunteer id PK - unique number, auto number vol_name * f_name * s_name * house * street1 * street2 street3 town * city postcode * p_home * p_work p_mobile email last_change date/time, when record last updated vol_link_lan vid FK, PK lid FK, PK level qua language id PK, Auto name * vol_link_rol id PK, vid FK rid FK sdate * edate * role id PK, auto name * description vol_link_sta id PK, vid FK sid FK sdate * edate * status id PK, auto name * description I am not sure if the vol_link_rol and vol_link_sta are correct, I have created a PK to ensure there is a record of the volunteer and the status and the same for the roles table. comments please.
View Replies !
View Related
Table Design
I have been asked to create a simple 5 question quiz and can not for the life of me figure out the table and form design. The goal is to have a question listed and then 5 answers to choose from. I need the answers to be in an options control format meaning they will read the question and check one of the option boxes for their choice. I need it allow only one of the 5 to be chosen...that is the problem. If I make each answer a field then they can check all of them and I don't want that. I don't think this is that difficult but would like to stay away from coding it as I am very good in access except for the code part. Thanks in adavance!
View Replies !
View Related
|
TRACKER
