Inventory location question

[liquid One]

Im setting up a db for business software for a Audio video company. I have
about 3000 items in inventory, and there are currenlty about a dozen locations
that they store inventory at. I need to track where the items are at any given
time. They are all generic items though, no serieal numbers etc need to be
recorded. This is what I have set up so far.

Table: inventory
-inv_id
-inv_item
-inv_desc1
-inv_desc2
-inv_type -product vs warranty or service, etc
-inv_ass_acc -assett account for accounting
-inv_cogs_acc -liability account
-inv_cost
-inv_price
-inv_sale_price
-inv_tax
-vnd_id -manufacturer id that links to vendor db


Table: quantity
-qty_id -PK
-inv_id -links to inventory item
-loc_id -links to location table
-qty_amt -number, amount of items at that location


Now when salespeople do a search for items, I want it to list the item name
description, and stuff, and then how many it has in stock at each location. So
the thing is, it needs to pull one record for each item that matches. but then
it needs to pull about a dozen records for each item from the quantity table.
An average search might have a dozen results, but could have up to a 100 or so.
Im curreently using verity for the search, to allow them to search through the
item, and both description fields. But if I need to, I'll use SQL to do the
whole seach, I just need more input on people that have done this.
-What is the best way to do the search for this use, verity or SQL?
-Is there a more efficent way to set up the DB than I have it?
-How would I get multiple rows from one table to display on 1 row from another
table?
-please give me some sample code if possible, im not terrible great with sql.

Thanks,
Mike

[Dan Bracuk]

You'll need to learn sql. I hear Teach Yourself Sql in 10 Minutes by Ben Forta
is good.

Your database design is not bad. You do want to address the following issues
though.

Some data changes with time. Examples are the price of an item, and how long
an item is in a location.

Your inventory table seems to have too many fields. I am not sure what the
difference is between inv_item and inv_description1.

You might want to have related tables for inventory type, asset account, and
liablity account.

I would design the quantity table like this:

inv_id PK and FK to inventory

Good luck. This is an ambitious project for someone who is not terribly great
with sql.

loc_id PK and FD to location
date_starting PK
amount

[liquid One]

Hey,
This is a very ambitious project, and I do need to learn more sql, and if you
knew the entire project I was doing, even more so. Im trying to only ask
questions one at a time though to help me through. But the most important
question I had didnt get answered.
How do I get multiple locations to pull up for each item that returns from a
verity search?

btw, the 2 descriptions are neccessary. The item name is a short version of
the product, just make and model. Then description 1 is for customers to see,
and description 2 is for employees and vendors to see that contains information
that the customer either doesnt need to see, or we dont want them to see.

Also how long an item is in inventory is irrelevant since they are all generic
products. There are related tables for the accounting fields, and type,
although i wasnt sure i should even do that because the only thing that needs
to save is the name, which will never change because I will be following the
standardized account naming convention. Any changes in price are going to be
recorded in a historical inventorychanges table that will log all changes to
any record in the inventory, including price.
Please help me with the Coldfusion needed to pull the information correctly.
Thanks

[liquid One]

bump, someone please help me, thanks