do i create 12 tables for 12 forms or one table

[sunilvedula]

hi all,

i am trying to get normalization concepts right in my desing of database so as to avoid wastage of space and corruption of data.

i have 12 queue (people work on this data which they get from different sources) an they will require to enter informatin related to the queue in the database.

the information that is required to be entered for each of the queues is different but there are some common fields.

so do i create a single table which contains all the fields not repeating any of it though by creating a primary key with autonumber, queue name and the employee id(which is unique) .

or do i create tables by grouping common fields and creating tables for queues for differnt fields?

which would be a better desing what would be the best way to follow?

 

[HiTechCoach]

I find that properly normalizing your table structures really help with usability, flexibility, and handling future needs.

I would have a single table for all the the information that is required to be entered for each of the queues. I would have one record per item entered.

Example:

QueueInfoKey - AutoNumber
QueueKey 
EmployeeID (lookup from a list - could be filtered by the QueueKey)
QueueInfoKey (lookup from a list - could be filtered bu the QueueKey)
QueueInfoTest  - this is the data entered

This will allow as many pieces of information per queue as needed. No limits.

To add an item, you wou not add a field as per your design, but simple ass a record to the lookup table.

I would use cascading combo boxes to limit the lists to what is needed for that queue.

Hope this helps ...

 

[georgedwilkinson]

I think you're wasting your time worrying about forms until your database is properly normalized. And properly database normalization has nothing to do with how you think the data might be presented.

Talk to the user community; find out what they need the system to do (not what they want it to look like); create your entities, attributes, and relationships; make them into a normalized table design; then worry about what the forms and reports look like.

 

[sunilvedula]

yeah you are right. i should not be worried about forms but about the basic table design. so now i create a table for all the queues with sno being the autonumber and the primary key. but my single doubt is there might be fields left without being used when they fill information for other queues. *is that ok. would it not be against normalization concept leaving some fields empty wastage of space. just thinking?

 

[HiTechCoach]

... but my single doubt is there might be fields left without being used when they fill information for other queues.

This should not happen if designed using the method I suggested above. Every record will be completely filled out if created.

 

[MarkK]

My 2c is it's not the end of the world if you have fields that are empty. Maybe some data is not available in certain cases. This is acceptable. Look at how many fields Microsoft offers you when you store an Outlook Contact!
Also, feel free to post your table design. It'll save you a ton of headaches to get your tables as correct as possible as soon as possible.
Cheers,

 

[sunilvedula]

Also, feel free to post your table design. Cheers,

hi i am uploading my db deleting all other 11 forms and other tables. this db contains main table which is used for all the 12 forms. i did not feel the need to create multiple table to store data. pls have a look at the design and let me know your thoughts and where can i improve or if this design is fine.

 

[HiTechCoach]

hi i am uploading my db deleting all other 11 forms and other tables. this db contains main table which is used for all the 12 forms. i did not feel the need to create multiple table to store data. pls have a look at the design and let me know your thoughts and where can i improve or if this design is fine.

I tried to look sat your database. It get a AutoExec error and locked up Access. I have to End Task on it. I then had to reboot to get Access to work properly. Glad I tried this on a test machine!

EDIT:
The issue is a missing reference to C:\REACH\ClientAPI.DLL Do you need to include the DLL with your database for it to work?

 

[MarkK]

What discrete things exist in your system? Consider that you are modelling a real-world process or system. Within that system there are things that have properties and behaviours; things or objects that you'd describe using nouns.
- Your Employee table works in this respect since each record will describe a distinct instance of the type of thing 'Employee'.
- Your Track table fails this test. Track is a verb and maybe it's something an employee can do, but this table does not describe a specific type of object. A single thing cannot have a Credit Limit, an Amt, a DispAmt, TranAmt, FraudAmt, and a USDollarAmt and still be identified as a representation of a discrete object. This table must be broken into a few others at least.
- And there are tables I would expect to see, like Customer, Account, Transaction, Complaint. And what about the Queue you were mentioning? What is queued? Maybe this is two things, the Queue itself and memebers of the Queue.
- Some things you might think are objects are not really. In a warehouse you might have a queue of pending orders to be filled. You call it 'The Queue,' and everybody's job is to fill the next order in 'The Queue.' This is not a 'thing' or a table in your data system. This is a subset of things (maybe Orders) that have a specific status or attribute (maybe Pending, or Unfilled).
- In the existing system are there forms to fill out or that get produced? Sometimes each form or structured document in an existing paper system can become a table in a data system. Applications, Statements, POs, Orders, and so on...
Hope this helps,

 

[sunilvedula]

EDIT:
The issue is a missing reference to C:\REACH\ClientAPI.DLL Do you need to include the DLL with your database for it to work?

Hi All,

My Apologies for not uploading the client api.dll. I am uploading it now.

- Your Track table fails this test. Track is a verb and maybe it's something an employee can do, but this table does not describe a specific type of object. A single thing cannot have a Credit Limit, an Amt, a DispAmt, TranAmt, FraudAmt, and a USDollarAmt and still be identified as a representation of a discrete object. This table must be broken into a few others at least.
- And there are tables I would expect to see, like Customer, Account, Transaction, Complaint. And what about the Queue you were mentioning? What is queued? Maybe this is two things, the Queue itself and memebers of the Queue.

As for the table employees you need to add the way you login into your system if by chance it is not allowing yu. In the HCSID fields add the name throught which you login. now coming to the point of the queue this is the name of where we identify which one each one is working. I will try to explain. There are queue names like fraudqueue, acquiring queueu etc .... There are few people who work in each queue. When they submit an item it takes the queue name, empID (who submitted) and all other fields in line with the queue. As for the table employee it is used to only login and determine who is logged in. sorry i forgot to input the table queue. i have a table queue which gives all the queues names. Now in table track each record contains all the variables in line with the queue. If fraud queue is mentioned then the fields that will be filled in are empid, inputdt(these are compulsory for all the queues) then transamt, no of charges, etc... it goes on. IF someone is filling for New disputes queue then the queue name is NewDisputes, empID, Inputdt, ARN, CARDNO, AMT, and other fields..
can now you explain me what is wrong?

 

[MarkK]

Here's a structure that might resonate with you. Every item in every queue might be called an 'Issue'. Data that all issues have in common go in the Issues table. Data that are specific to a type of issue then belong in that specific table.

tIssue
IssueID (PK)
EmployeeID
DateReceived
Status

tFraud
FraudID (PK)
IssueID (FK)

tDispute
DisputeID (PK)
IssueID (FK)

Some common data are shared, and distinct data is not.

But in answer to your original question: I would use multiple tables for the different type of things, not one table like your track table.
Cheers,

 

[sunilvedula]

You have clarified me in many things. Thanks to it. just a few more.If that is the case would i not end up having 12 tables because each queu can end up in have atleast one distinct field. Too many tables is tht not an issue? having so many tables how will it help the performance of the database?

 

[Galaxiom]

More tables is faster because only those tables and records relevant to the record in the main table are returned. Sounds like it is probably the way to go in your case.

However, different fields of the same data type can be recorded in the same table. It is a bit more complex than separate tables and really more suited to recording values for a variable number of diverse aspects against the same main record.

An Aspect table has records for each aspect type that would have otherwise been stored as a field in the main table. Its fields are AspectID and AspectName. Another table has IssueID, AspectID, AspectValue. One IssueID can have any number of Aspects assigned to it.

It is most easily implemented with the aspects displayed as a subform in datasheet or continuous mode. It can be done with ordinary single record subforms and even made to look like just a main form if you want but that requires some finesse.