|
Hello, CP. So I am working on this application here at work and the current piece involves inventory management. First, I will list the tools I use:
Visual Studio 2010 Ultimate
C#, .NET 4.0
Microsoft SQL Server 2008 Enterprise
LINQ To SQL
Now, what I have are various types of items which need to be tracked. They are referred to as "RPCs" (and they are all computer parts). I have been thinking on this for a while but I need to figure out the most efficient way (both from a development standpoint as well as from an end-user standpoint) to input data and store these items in the database.
What I have done so far, for the first three types of RPCs, is I have created a UserControl. On that control are the fields necessary for that particular RPC type. Each RPC has various attributes which are not shared among other types. For example, a Hard Drive has Marketed Size, Physical Size (2.5" or 3.5"), RPM while a CPU has Clock Speed, Cache Size, # of Cores. I think you get the idea.
Well, I started out with these three UserControls as well as three tables in the database, one for each type. However, the issue really becomes known when I say this: to start with, I have roughly 25 diferent RPC types.
So is there a way I can keep from creating 25 UserControls, one for each type? Or 25 tables in the database just for RPCs? I thought about creating a single table with the specs that each type does have in common (e.g. Manufacturer, Model) an storing the specs in a specific format (e.g. XML) in a column and having the application manipulate the specs data as necessary. However, the performance issue arises when I think about the fact that I must be able to search various RPC types by one or more specs. For example, the application needs to be capable of finding all 500GB/7200RPM hard drives. That wouldn't work so well when using XML for all of the specs would it?
Maybe I am going about this whole thing all wrong. But then again, maybe I am better off going with the numerous tables and user controls. If that's what it takes, I'll do it. I just started thinking about it early on and realized that maybe I am not being entirely efficient with it.
Any suggestions? 
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
Matt U. wrote: but I need to figure out the most efficient way (both from a development
standpoint as well as from an end-user standpoint)
Then you really need to have some more requirements.
For example will 100,000 users access this?
Will there be 100 billion of these?
Will there be 1000 retail locations attempting to access it?
Questions like that impact the design.
Matt U. wrote: So is there a way I can keep from creating 25 UserControls, one for each type?
Yes.
Is it ideal in terms of your actual business requirments? I have no idea.
But the idea is simple you have a inventory item with "properties". In the simplist scenarios there business needs for the properties are very simple so consequently one can store them in a property table.
That table would look like this
Property (primary key id is assumed)
- inventory_id
- name
- type
- value
That table would have a row like the following
<some inventory="" id="">, "Clock Speed", "Decimal", "2.66"
There are many variations on the above. For example instead of "Clock Speed/"Decimal" you could have an enumeration table that define a 'type' and then the property id would tie to that type via the type id.
|
|
|
|
|
First of all, thank you for the response.
jschell wrote: For example will 100,000 users access this?
Will there be 100 billion of these?
Will there be 1000 retail locations attempting to access it?
There will only be a handful of people who access this portion of the system.
There will only be roughly 25 item types. However, there may be several variations of each item type. (500GB 5400RPM, 500GB 7200RPM, 750GB 5400RPM, etc.)
There is only one facility which will access this application. It's all in-house/local.
jschell wrote: But the idea is simple you have a inventory item with "properties". In the simplist scenarios there business needs for the properties are very simple so consequently one can store them in a property table.
So if I were to implement this idea would I have the following?
Inventory Table
------------------------
- InventoryId
- Manufacturer
- PartNumber
- ItemType
- TotalQty (this is the total qty. of this item in inventory)
- AvailableQty (this is the qty. (must be <= TotalQty) of this item which are available for picking)
Properties Table
------------------------
- InventoryId (linked to Inventory Table)
- Name
- Type
- Value
Am I understanding it correctly? Also, as far as not having that many UserControls, how could I reduce that number? I'm still thinking on that one and trying to figure it out. 
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
Matt U. wrote: Am I understanding it correctly?
Yes.
However why are the other fields not properties as well - like 'part number'?
Quantities are probably functional so those wouldn't be properties.
|
|
|
|
|
Well, I figured since the 'Manufacturer' and 'Part Number' fields were common among all items, why not have those fields in the base table. Would there be any real advantages of placing them in the Properties table? Or is it more for separation of purpose?
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
Purpose not commonality is what drives the design.
Part number might be broken out because you need to drive functionality from it - like queries and/or B2B work. I doubt Manufacturer woujld have that same need.
However it is still somewhate subjective especially since your volume is low. You might just keep them there to make it easier for the DBA to investigate stuff.
|
|
|
|
|
I understand, that makes sense. Oh, it will make it easier for me to investigate stuff? LoL. I run the entire show when it comes to designing and developing software here. I design the databases, maintain the servers, write the software, etc. Haha. But I understand your point. Thanks a lot for all of your input.
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
In one company I worked at, we coded these characteristics into the .part number itself.
For instancr, a 4000 series graphics chip could have a speed rating of 24, 32 or 40 MHz.
It would be numbered as 4000-24, 4000-32 and 4000-40.
In the case of your CPU, the part number could be i3-1.4-c2-v2 for Intel i3, 1-4 GHz, 2-core, version 2 model.
In this case, one needs to only have the part number entered correctly into the system and not have to worry about some Tom, Dick or Harry entering all the characteristics in a separate property sheet.
Your disk drive could be numbered DISK-2.5-500-7200 for a 2.5", 500GB, 7200 RPM drive. You can even add SATA, PATA by using an S or a P at the end.
Don't know if this would meet your needs or not.
|
|
|
|
|
I had already taken that into consideration. However, the part numbers come from corporate. We have no control of them whatsoever so we're a bit limited in that aspect. :-/ I figured it out though, using the separate table as mentioned.
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
We used to supply parts to the Itty Bitty Mitty Computer Corporation who wanted to see only their part number on the chips as well as on the shipping documents.
Our Inventory Control system allowed for alternate part numbers so the user could put in their part numbers and the system would still point to the one that we used inside our company.
|
|
|
|
|
Oh, interesting. Well, our current implementation seems to fit our needs. But I'll keep this idea in mind. Thank you, Vivic.
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
