Results 1 to 10 of 10

Thread: database design

  1. #1
    Join Date
    Mar 2007
    Posts
    45

    database design

    I am working on a database for my friend to help him keep inventory on his weapons.

    I have developed a relationship diagram and I wanted some other opinions on it.

    Check it out!

    http://i244.photobucket.com/albums/g...g?t=1229668062


    Any advice will not be taken personally. Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    What does your friend want to know about his weapons, customers, and their relationships? This model won't deal well with a garrote, a trebuchet, a lance, or a knife, several of which were popular with the Apache.

    -PatP

  3. #3
    Join Date
    Mar 2007
    Posts
    45
    Apache is the name of his store, it is not relevant to the data. I appreciate the post.

  4. #4
    Join Date
    Mar 2007
    Posts
    45
    are there any other suggestions?

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Guiding light

    Maybe this data model can guide you (or some other one on this site).
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Are trebuchets legal in the U.S., as long as they are not fully automatic?
    Also, can you get a concealed carry permit for them?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I've never seen a semi-automatic trebuchet, and never contemplated a fully automatic one. I'm having trouble picturing the trenchcoat necessary.

    -PatP

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    didn't the Romans have a semi automatic siege weapon.. the ballista. its not quite the small lethal package you can stuff under your leather jacket to do you own remake of Terminator or use in the innovative way of going down to the bank to withdraw some cash
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2008
    Posts
    89
    Ok, to come back to the question...
    If your friend has a business (and the table name CUSTOMERS hints in that direction), you may want to consider also using one or two price columns in the weapon table (like rental_price and sale_price for example), so he can keep track of his finances this way as well.

    Or you use a separate table, if the pricing is more complex than that and FK-it to the weapons ("record_number" is a horrible name choice... use something like "weapon_id" or so).

    my two cents...
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    A manufacturer table (id, name, addresses, etc.) fk'd to the weapons table?

    Note that until you assign a customer, you can't have the intersection table record...

    How do you handle the situation when the weapons have not yet been sold?

    what about:
    Weapons table. descriptive data about the weapon (but, not including serial number, or any other information specific to a given weapon)
    SNs table. FK to the weapon table. WeaponID, SN, Comment. PurchaseCost. SellPrice. This table contains ANY data which applies to a specific weapon.
    Locations table. LocationID. Location Descr.
    Manufacturers table. data pertaining to the manufacturers.
    Customers Table.
    Inventory Table: WeaponID. SN. Location ID.
    Transactions Table. WeaponID. SN. TransactionDate. TransactionType. FromLocation. ToLocation. Comment.
    Transaction Type table. Sale. Receive. Move. Adjust.

    When you receive a weapon, you add a SN record, and a weapon record, if this is the first time you're received a weapon of this exact type. Then, you add a transaction record, with a ManufacturerID in the From Location, and Receive transaction type.

    When you sell a weapon, you add a transaction record with a Sell transaction type, qty=1, and the customer ID in the To Location.

    When you return a weapon (it has a defect...) add a transaction record with a RETURN trans type, and mfgr ID in the TO location.

    Somebody breaks in and steals a weapon. You add an inventory transaction record to adjust inventory (down by 1). Null TO location, ADJUST inventory code.

    If you move a weapon, the FROM inventory location decreases by 1, and the TO inventory location increases by 1.

    This way, you could track a weapon from receipt through sale, and every step in between.

    This is the approach for serialized parts. If the parts aren't serialized, (ammo, slings, etc.) you could use the same approach. The quantities can be other than one if there's no serial numbers associated with the part. I would have a item or part table, rather than a Weapon table, in fact. Have a field in the parts table to specify whether the part is serialized.
    Last edited by loquin; 01-14-09 at 13:19.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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