Hello everyone,

I am still learning database design principles and methodologies.
I am also currently learning Oracle 10g and PL/SQL (don't hold it against me MSSS users )

Anyhow, I have started to design a database to be used as a back-end to a user friendly application. Here is what I have so far, please let me know what I am doing horribly wrong or maybe even right.

Corporate Inventory database for a company that does a lot of field service work with local parts, but would sometimes require parts to be shipped from other locs. All part transfers will be initialized by a loc to loc phone call, if the authorized user at the requested loc confirms they have the part and can spare it, they will then perform the Transfer transaction. The transfer transaction consists of the Manager inputting the partNo to be transferred, the current location (curLoc), and the destination location (newLoc).

DB would track inventory for all offices throughout company, it would be an internal system and application. Here are the different types of users who will be using the application. It will use a front end with user forms which will allow the current user to perform tasks they are privileged to do, based on their roleType.

Basic users can only VIEW inventory and other information for each office.

Mid-level users can ALTER item totals at their local office that have been used with or without transfer.

Management users can ALTER item totals, UPDATE/DELETE/INSERT users, add new itemNo to inventory, remove old itemNo from inventory.

Executive users can do all of the above, as well ALTER prices, ALTER manID, DELETE manID, INSERT manID.


Entity tables:
Part (partNo(PK), partName, partDescr, partCat(FK), partPrice, totalQty)

PartCategory (partCat(PK), catDescr)

PartQty (partNo(PFK), locID(FK), totalQty, locQty)

Inventory (invSurKey(PK), partNo(FK), partCat(FK), partDescr, partPrice, locID(FK), state, locQty, totalQty)

LocInventory(locInvSurKey(PK), partNo(FK), partCat(FK), partDescr, partPrice, locQty)

Location (locID(PK), manID(FK), address, city, state, zip, phoneNo, region)

Managers (manID(PK), locID(FK), manFname, manLname)

Users (userID(PK), roleID(FK), uFname, uLname, uLogon, uPassw)

Roles (roleID(PK), roleType)

Transaction recording table:
Transfer (tranID(PK), partNo(FK), date, curLocID, newLocID, manID(FK))

curLocID and newLocID will be entered in a form by user.

I have not decided on a front-end. Any suggestions if I am using Oracle 10g? I was thinking maybe JDeveloper and Oracle ADF.
Once again please provide any insight into this design you may have.

Best Regards,
Jason

My only comment is that you don't need the Managers table, since a manager will also be a user Locations.ManID can be a foreign key to the users table. This of course assumes the Roles table has a role of Manager.

You don't say how you are defining your keys. I had to make some assumptions that some are sequentialy assigned numbers and others are composits. It would help if you specify how your keys are defined.

Thanks for the reply Mark. Do you mean how they will be defined in the code?

Which did you assume were composites?

I usually define my keys as serial numbers that the database assigns when it creates the row (I forget what Oracle calls them). I am not sure if the key to your tables is a sequential number assigned by the system or something the user enters (the key to the Users table could be an employee number).

locInvSurKey looks like a composite key made up of the key to the location table and the key to the inventory table. If it is a serially assigned number you would need foreign keys linking the LocInventory table to both the location and inventory tables.

Since you didn't define whether the keys are database assigned numbers, entered by the user, or created in some other fashion I had to make some assumptions.

Now I understand what you were asking. All of the primary keys are predefined and will be unique, most likely sequential, numbers. The locInvsurKey is actually a surrogate key, I wasn't sure how I would define these. I wanted to have a unique key for those tables which have a surKey, because those tables will have multiple rows with the same FK value, but different values for the attributes :
(i.e. Inventory (invSurKey(PK), partNo(FK), partCat(FK), partDescr, partPrice, locID(FK), state, locQty, totalQty) could have rows with the same partNo, but different values for locID, locQty, and totalQty)

My problem is, how can I make sure that these three tables below have the proper relationship with locQty, totalQty?

PartQty (partNo(PFK), locID(FK), totalQty, locQty)

Inventory (invSurKey(PK), partNo(FK), partCat(FK), partDescr, partPrice, locID(FK), state, locQty, totalQty)

LocInventory(locInvSurKey(PK), partNo(FK), partCat(FK), partDescr, partPrice, locQty)

Thanks again for taking the time to help me.

I'm not sure you need all these columns in Inventory and LocInventory:

partNo(FK) is enough to get partCat(FK), partDescr, partPrice from Part table. So, assuming they are same data, I would suggest:
Inventory (invSurKey(PK), partNo(FK), locID(FK), state, locQty, totalQty)

LocInventory(locInvSurKey(PK), partNo(FK), locQty)

Hope this helps...

The only thing is I want the user to see all of that information in Inventory. Would it be better to put all of that information into a view, and use that view to display all of the information desired?

Here is how I would design the tables:

Region – regionID(PK – autonumber), regionDescr
Roles – roleID(PK – autonumber), roleType
Users – userID(PK – autonumber), roleID(FK), uFname, uLname, uLogon, uPassw
PartCategory – partCatID(PK – autonumber), catDescr
Part – partID(PK – autonumber), partName, partDescr, partCatID(FK), partPrice, totalQty
Location – locID(PK – autonumber), managerID(FK to Users), address, city, state, zip, phoneNo, regionID(FK)
LocInventory – locInventoryID(PK – autonumber), partID(FK), locID(FK), locQty – (unique index on partID, locID to form AK)

Transfer – tranID(PK – autonumber), partID(FK), date, curLocID(FK to Location), newLocID(FK to location), approverID(FK to user)

I have eliminated the duplications of information and have explained the keys a bit more. I have also eliminated a table or two that you can derive by doing some views.

Yes, you would want to create views to gather the information together instead of duplicating the part number, description, etc. in all of the tables.

These are just things that popped into my mind when looking at this:

• There's a managerID in Location telling you that a given user is a manager but this info is also present in the userRole field. So what field would your permission system use? What would happen if a user is set up as a manager via the managerID but not in userRole? Personally I'd just use userRole.
• Do you really need a roleTypeID field - wouldn't just using roleType be simpler (but keep the lookup table)?
• It might be good to add a Log table so you can keep track of what was changed by the various users.
• Is it worth having a parentPartCatID field allowing you to use a hierarchy?
• Would it be clearer to just have Category rather than PartCategory?
• The date field name should be changed to avoid possible key words and should there be two dates - one for when the part was requested and one for when it's expected?
• Should there be more user ids in the Transfer table ie a requestorId and an id for the guy that entered the data?
• Should all users be associated with a location?
• Often User tables have a managerID which can be useful when transferring permissions.
• Would you ever require an alternative part field ie if DD2 memory not available then use DDR3?

I'm afraid I only quickly glanced through the previous posts so please ignore if there's reasons for certain approaches.

Mike

Answer to Mike Bike Kite, point by point:

The manager ID in Location tells who the manager of the location is. The security should work from the User and Role tables.

The roleTypeID is unneeded at this point, but I tend to put IDs on all my tables to provide keys for new relationships when the system is enhanced. It also prevents problems if the spelling of roleType changes.

A log table is probably a good idea.

I am not sure about parent parts. The original poster didn't mention the need for a hierarchy.

I used the name PartCategory because sooner or later there will be a CustomerCategory or a SalesPersonCategory. Systems always get added to.

Yeah, avoiding reserved words is a good idea. A date requested and date expected might be useful depending on what the requirements of the system are.

It is a good idea to add a lastUpdatedDate and updateUserID(FK to user) on all your tables, but it depends on who is designing.

The last three of your points would have to be answered by the original poster. I don't know enough of the requirements to comment.

I suppose this one boils down to personal taste. The only downside is slightly more complex SQL and needing to join to another table each time.

These fields are fine and would show who changed the record last but wouldn't show any previous history or who deleted an item.

Yes, all users will be associated with one and only one office location.

Do you mean a managerID independent of manID to represent who the manager of the user is?

No, the parts have specific uses and are not interchangeable.

Thanks for the input guys, I am going to attempt to create a new list of tables and see what you two think about them.

Best Regards,
Jason

newbie_dev

I thought Mark's design was fine, I was just raising a few small questions. You might want to add locationID to the users table. It might also be worth adding phone and email as well. I didn't see a manID field but perhaps that was accidentally missed.

As a wild suggestion you might also want to have some way of storing (or working out) the distance between locations. This way you could show the user the products that are closest to him first and those furthest from him last.

If you don't already have a database server then I'd suggest just using MySQL and PHP to do the web screens - software and staff are much cheaper. A web app means you don't have to install anything on users PCs and it allows a manager to check his stock on his iphone as well as on his office PC. I'd consider hiring some young gun to do the software for you - if the software's going to be useful to you then it won't take him long to produce and you'll be able to get everything running quicker. Obviously if you're currently using Oracle then I'd just keep using it.

Mike

This is a great suggestion, I will look into implementing this feature.

Yes, as of right now, Oracle is the system that will be used.

I will be the one designing and implementing the application, as we do not have enough resources or budget to hire a real professional. I will most likely be the main administrator, or share duties, until someone more qualified comes on board.

I have not really had too much time to work on the design as of late, due to a higher priority, unrelated application being developed.
I will be using this thread and forum for resources and information as the development progresses.

Best Regards,
Jason

Hi !
I've nothing to say about the model Mark and Mike built (fine IMO), but just two things about your requirements:
Your initial inventory table suggested me you could like to keep some snapshots of your inventory. BTW, it's easy to add now or later, and would'nt change anything in the model.

Prices is a potentially more serious issue. You have only one here, and in common stock management, they are many. Maybe you need not to deal with prices accurately, but I think you should think on it.

Regards,
Laurent