October 26, 2009 at 11:09 pm
Hi All,
Good day to all of you..
I would like to seek advise from all database experts here regarding how to go about with designing my database table structure.
I am designing for a Tools Withdrawal Management System for a particular ToolCrib hub. A toolcrib hub is a place in the school where all the schools Tools being used by the
personnel are stored.
In order for borrower to borrow certain tools that they need in their job, they need to sign up
the details of the tools that they want to borrow.
The custodian or the personnel assigned at the toolcrib hub will check the details of this request and then find the tool according to the details created by the borrower.
The custodian needs to sign something at the remarks that the transaction has been granted.
Basically here are the details:
1. Each tool has a unique set of attributes like ToolA has Tool_Item/Tool_length/Tool_Dimension. ToolB on the other hand has Tool_Item/Tool_PadSize/Tool_Radius. ToolC has
Tool_Item/Tool_Range/Tool_Guide. Each Tools has different attributes but sometimes two tools share some attribute. In this case Tool_Item is the same and they have a
convention that these are the item type of the tools that they can put at the Tool_Desc column.
2. Whenever a borrower transacts a request into the toolcrib, he needs to fill in the info of his requested tool. They call this request information. Also, each request should have an associated return information that they need to fill in also. A return information refers to the tool that they will return to the custodian if they want to borrow another tool with a
different size or dimension. If they have nothing to return then they can just fill in 'N/A'
Example, in the morning I borrowed ToolA with 12in/TightScrew/1.2mil so I have to fill in below info:
Req_Tool_Length | Req_Tool_Item | Req_Tool_Dimension | Ret_Tool_Length | Ret_Tool_Item | Ret_Tool_Dimension
12| TightScrew | 1.2 | N/A | N/A | N/A
But in the afternoon, I need a different dimension tool like 16in/TightScrew/1.0mil , so I have to fill below info in paper:
Req_Tool_Length | Req_Tool_Item | Req_Tool_Dimension | Ret_Tool_Length | Ret_Tool_Item | Ret_Tool_Dimension
16| TightScrew | 1.0 | 12 | TightScrew | 1.2
Legend:
Req -> Request
Ret -> Return
Each borrower is not allowed to borrow two or more tool of the same item type at the same time.
Now here is my problem, If only they have one tool item then I am planning to have a table such as this:
RequestTable:
ColumnKey Sample Data(No Return)Sample Date(With Return)
BorrowerIDPK '0001456''0001456'
Date PK'10/26/2009 10:ooAM''10/26/2009 2:ooPM'
ToolID 11
Req_Tool_Length 1216
Req_Tool_Dim 1.21.0
Ret_Tool_LengthNULL12
Ret_Tool_DimNULL1.2
StatusID11
Status: (Lookup table)
ColumnKey Sample Data
StatusIDPK1
Status_Desc'Pending' or 'Serve'
Tools: (Lookup Table)
ColumnKey Sample Data
ToolIDPK1
Tool_Desc'TightScrew'
The StatusID will display if the following request has been serve or still pending to be serve by the custodian.
But the users of this system wants a GUI that will display all transaction such as below including all the tools attribute:
Columns:Sample DataSample Data
(Request For ToolA with No Return Info)(Request For ToolB with Return Info)
BorrowerID '0001456''0001456'
Date '10/26/2009 10:00AM''10/26/2009 10:00AM'
Item TightScrew'Bonding Pad'
Req_Length 12'N/A'
Req_Dim 1.2'N/A'
Req_PadSize 'N/A''12 X 12'
Req_Radius 'N/A'1.1
Req_Range 'N/A''N/A'
Req_Guide'N/A'N/A'
Ret_Length 'N/A''N/A'
Ret_Dim 'N/A''N/A'
Ret_PadSize 'N/A''24 X 24'
Ret_Radius 'N/A'2.0
Ret_Range 'N/A''N/A'
Ret_Guide'N/A''N/A'
Status'Pending''Pending'
Also this is how they are currently filling in their request info. They are requesting also a report of this format.
Now, I dont know how to go about with my design. My first option is to create a separate table per tool item or to go about with this single table design that holds all of the tools attribute.
For option 1, the tables will be normalized but I am not yet certain how to create the query to display all the tools attribute at once. The query will also be a join of multiple tables. Say there's a table for ToolA only with all the ToolA specific attribute. Another for ToolB and so on.
For option2, querying would be ok since everything is laid out already. My only concern is that if many are transacting in my table, there will be many query that will insert records while there will be query that will perform the select statement.
Please advise on my initial design. Sorry also if its a bit long, I tried my best to make it concise but still easy to understand.
October 26, 2009 at 11:27 pm
Uh, this looks a lot like homework.. We don't do homework..
CEWII
October 27, 2009 at 4:36 am
Hi,
This is not my homework. In fact, this is my first attempt to design my first database structure. It just happen that the school nearest to us needed something like another way to help their daily routine.
I found this as an opportunity to apply some of what I have been reading.
I came not from a programming course or software engineering program but it just happen that when I started to work, I was fascinated on all the software program around.. so I started to learn how to program. I just find the need to store my data so I started learning databases.
If you reread my post again, did I ask you to design the table for me?
I had my initial tables designed by me which I got from my little understanding on what I have been reading on database design.
I was just asking for some advise or criticism on what I have done since I have been reading lots of helpful comments from the people around this forum.
Maybe because my post was so long that you think this is a homework. Its just that, I can't say things simpler so I inserted all the details.
Thanks for your time in reading anyway..
October 28, 2009 at 1:27 pm
I would lean towards keeping the different tool attributes out of the requests/returns tables, otherwise you may need to add a lot of extra validation to ensure that appropriate values are entered for these fields based on the tool-type (from your example, if requesting a Tight-Screw, you might then need to ensure the length/dimension are valid values)
Because the attributes can vary between tools, and to keep things simple, I would then suggest an Attribute column on your Tools table - a text/descriptive field where you can enter length/diameter/etc as required. When selecting a tool (request/return) the tool-description and attribute details can be displayed for users to select the correct option. No invalid options can then be selected/entered.
Also, I would then suggest using something like a tool-type column in the Tools table - that way you can group all the same types of tools together (each with differing Attribute details), then it is easy to check when someone requests a particular tool whether they already have another tool of the same type which hasn't yet been returned.
If this is homework, no problem - everyone needs help with that. There is a difference between helping, and doing. Homework, or not, the actual table designs are still in your hands.
Chris
October 28, 2009 at 1:30 pm
From what I could gather from reading your question, you need to have following tables:
1) Table to store all the tool information.
2) Borrower table.
3) Lookup table(s).
4) Transaction table with ToolID and BorrowerID and ToolType as the PK with the column No_of_times_borrowed.
You could use a trigger then to see if the user is borrowing the tool for than once.
As regards the reporting requirements you can do a union of the tables and display in a view.
Hope this helps.
Amol
Amol Naik
October 29, 2009 at 7:27 am
Hi,
Thanks to both of you for taking a second look on my question. I really am getting some information regarding your suggestion.
But allow me to clear up some items.
Each transaction made by the borrower has unique tool that he can withdraw from the custodian. So if the borrower will request fro toolA,
he then needs to fill in all of the information related to Tool A only. The rest he will set to 'N/A' or just blank.
This is where I am having lots of thoughts so I am thinking that I will just lay down all of the request transaction such as this one in
one table which I will call Request Table:
Request Table
BorrowersID | Date | StatusID | Tool_Type| ToolA_Attrib1 | ToolA_Attrib2 | ToolB_Attrib1 | ToolC_Attrib1 | ToolC_Attrib2 | ToolC_Attrib3
Then in each request transaction, I will just make it null if the Attribute is not applicable. I think this design is ugly and the whole
logic is concentrated on one table.
But then this is the type of Report that the user wants to see also. So if I perform the query it will be easier since everything is laid out.
I would lean towards keeping the different tool attributes out of the requests/returns tables
I like this idea, so I will remove all of the tools attribute in the request table. But where will I store the info on each transaction? Is it right that I will store it in another table then link both tables? What would be the fields on my request table and the other table?
As regards the reporting requirements you can do a union of the tables and display in a view
I think this will solve my reporting problem. I was thinking that each Tools represent a unique entity with each entity having different attributes. So if I create a union of all attributes then I would get my desired report format.
I just have a question about this also, what if I have 10 or more different tools, so it will be a union of ten tables?
Will my query be slow if the tables is filled with data?
I thank you guys for giving me a different way of thinking on how to design my tables. I actually will also be the one to create the application programs in java but I cant move on since my tables are still not set. Help me refine my thinking. 🙂
October 29, 2009 at 10:44 am
I just have a question about this also, what if I have 10 or more different tools, so it will be a union of ten tables? Will my query be slow if the tables is filled with data?
No it won't be slow, if you have the right indexes created on the tables, you should be able to fetch data in no time.
Amol
Amol Naik
October 29, 2009 at 12:57 pm
Mr. Newbie (10/29/2009)
Hi,I like this idea, so I will remove all of the tools attribute in the request table. But where will I store the info on each transaction? Is it right that I will store it in another table then link both tables? What would be the fields on my request table and the other table?
Good design practice results in information only being held once in the database - in this case, the tool attribute data - which can then be retrieved using table-joins. The requests/returns tables would simply have a foreign-key column that references the tools (or similar table) where the attribute data is held. Otherwise 100 requests for the same tool will have 100 copies of the attribute data.
Chris
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply