Best layout

[monion]

I am stumped as to the best layout of a database. It is an inventory database. In it are several (9) vendors each with 2-300 items that we order. Each item has an item#, description and cost. We will have 8 sites that place orders, though not all sites order from all vendors or should have access to order the entire vendor catalog of items.

Suggestions?

Thank you in advance
MO

[Atli]

Hey.

What have you tried so far?

The vendors and their items could be placed in two separate tables, where the vendors would be stored in one table, and the items in another. The items would be linked to the vendor table with a foreign key, which would indicate which vendor the item belongs to.
(A typical 1:N relationship.)

The sites could be placed in their own table, and a second table could be created that links the sites to the items they have access to. The link table would just contain two columns: a foreign key column linking to the sites, and another foreign key column linking to the items.
(A typical N:M relationship.)

Does that make sense?

[monion]

I used 2 table types
Table type #1 was named after the vendor, it contained the generic column names of: item#, description, cost and a column for each clinic (simple Y/N toggle)
I did a table of type#1 for each vendor.
I then just imported a .csv from excel that had the info and served it up as a basic webpage with one input field for each item so the user could enter a quantity.

Table type #2 was named after each clinic & vendor. Each column was named with item#, item#/description, item#/cost and item#/quantity.
The first three columns were from the table #1 info, the last column, item#/quantity was from my form input fields.

This just seems like the wrong way to be doing this...

As to your ?, I understand everything but the 2nd part of the 2nd part:
"..., and a second table could be created that links the sites to the items they have access to. The link table would just contain two columns: a foreign key column linking to the sites, and another foreign key column linking to the items."

[Atli]

@monion
That is indeed the wrong way to design a DB.

You tables and columns should never be named using actual data. Like creating individual tables for each vendor. You database should be able to scale perfectly without you ever having to alter the actual tables.

If you would have to create a new table or column to add data to your database, it is (usually) not designed properly.

@monion
Consider this:

+--------+ +--------------+
| vendor | | item         |
+--------+ +--------------+
| id PK  | | id PK        |
| name   | | name         |
+--------+ | price        |
           | vendor_id FK |
           +--------------+
 
+--------+ +--------------+
| site   | | site_item    |
+--------+ +--------------+
| id PK  | | item_id FK   |
| name   | | site_id FK   |
+--------+ | allowed      |
           +--------------+

The "site_item" table there is the important part. It links the items in "site" with the items in "item", so if you ever need to find out whether a particular site is allowed to sell a specific item, you would search the "site_item" table for the row that has the correct IDs and then just read the "allowed" value.

Does that make more sense?

[monion]

Would I make a seperate table just for the quantities ordered? and make that a FK to what? to display an invoice yielding Site name, vendor, item#, item price and quantity ordered.

Thank you for your patience...
MO

[Atli]

You could make an order system by adding two tables. The first on to list the actual order and who owns the order (which site), and a second table to contain the items in the order, which would be linked to both the order table and the item table, in a similar way the "site_item" table was.

Something like:

+------------+ +--------------+
| order      | | order_items  |
+------------+ +--------------+
| id PK      | | order_id FK  |
| site_id FK | | item_id FK   |
+------------+ | qantity      |
               +--------------+

[monion]

On the form submission, how would I go about submitting multiple items ordered? Would I just keep posting order_id, item_id and quantity over and over until all items off of the form have been submitted? I have only worked a form where a post went to unique fields one time.
ex.: lastname
firstname

not ex:
lastname
firstname
differentlastname
firstname
3rddifferentlastname
differentfirstname

etc.

Thanks,
MO

[monion]

Would I just pack all the values into one array and submit the array as one value?

[Atli]

You can do something like:

<intput type="text" name="firstname[1]">
<intput type="text" name="lastname[1]">
<br>
<intput type="text" name="firstname[2]">
<intput type="text" name="lastname[2]">
<br>
<intput type="text" name="firstname[3]">
<intput type="text" name="lastname[3]">
<br>

And PHP would read that as:

Array(
  [firstname] = Array(
    [1] = Value,
    [2] = Value,
    [3] = Value
  ),
  [lastname] = Array(
    [1] = Value,
    [2] = Value,
    [3] = Value
  )
)

So you could use JavaScript to allow the user to add as many <input> fields as he needs.