Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Join Date
    Oct 2009
    Posts
    14

    Designing a dB, looking for any insight

    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

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  3. #3
    Join Date
    Oct 2009
    Posts
    14
    Thanks for the reply Mark. Do you mean how they will be defined in the code?

    Which did you assume were composites?

  4. #4
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  5. #5
    Join Date
    Oct 2009
    Posts
    14
    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.

  6. #6
    Join Date
    Aug 2009
    Posts
    68
    Quote Originally Posted by newbie_dev
    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)
    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...

  7. #7
    Join Date
    Oct 2009
    Posts
    14
    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?

  8. #8
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by MarkATrombley
    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)
    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

  10. #10
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by MarkATrombley
    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.
    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.

    Quote Originally Posted by MarkATrombley
    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.
    These fields are fine and would show who changed the record last but wouldn't show any previous history or who deleted an item.

  12. #12
    Join Date
    Oct 2009
    Posts
    14
    Quote Originally Posted by mike_bike_kite
    • Should all users be associated with a location?
    Yes, all users will be associated with one and only one office location.

    Quote Originally Posted by mike_bike_kite
    • Often User tables have a managerID which can be useful when transferring permissions.
    Do you mean a managerID independent of manID to represent who the manager of the user is?

    Quote Originally Posted by mike_bike_kite
    • Would you ever require an alternative part field ie if DD2 memory not available then use DDR3?
    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

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  14. #14
    Join Date
    Oct 2009
    Posts
    14
    Quote Originally Posted by mike_bike_kite
    newbie_dev
    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.
    This is a great suggestion, I will look into implementing this feature.

    Quote Originally Posted by mike_bike_kite
    Obviously if you're currently using Oracle then I'd just keep using it.
    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

  15. #15
    Join Date
    Aug 2009
    Posts
    68

    History and prices

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •