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!

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

Apache is the name of his store, it is not relevant to the data. I appreciate the post.

are there any other suggestions?

Maybe this data model can guide you (or some other one on this site).

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?

I've never seen a semi-automatic trebuchet, and never contemplated a fully automatic one. I'm having trouble picturing the trenchcoat necessary.

-PatP

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

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...

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.