Simplifying a table

[jrmvt]

Hello,
I'm trying to figure out the best way to change my table design. Here's what I've got:
We offer to pre-price plants for our customers. My table was designed to hold retail pricing and SKU information for them. Program is Access 2007 FE hooked to an SQL 2005 Server. The fields are:
PlantID - Linked to the Plants table
CustomerID - Linked to the Customers table
RetailPrice
SKU

We grow about 2500 varieites of plants, so each customer has about 2500 records in this table. When we started out pricing for just 10 or 12 customers, this was managable. Now that we have over 100 customers wanting us to price for them, the table has become unwieldy and updates/appending new customers takes forever since the table is closing in on 400,000 records.

The only way I can think of is to break this into 2 tables (PlantPrices and PlantSKUs). Each table would have about 2500 records (1 for each plant type). Fields would be:
PlantID


CustomerID001
CustomerID002
CustomerID003
...
So each customer would be it's own field which held its price/SKU for that plant. I would need to add a new field to the table each time a new customer asked for pricing. I know this seems backward logistically, but I can't think of any way around it. Was hoping to see if anyone here has more ideas.
Thanks!
jrmvt

[ssanfu]

I see you got an answer from the UtterAccess site and can only echo them: DO NOT have a field for each customer!

I'm not sure I understand what "pre-price" means; let me run this by you.

You raise and sell plants to wholesalers (your customers). A customer wants 100 each Red Roses, White Roses and Pink Roses. You print the SKU and retail price on a label and attach it to each plant before it gets to the customer.

So, if that is what is happening, how do you get the price and SKU?

Do you send the list of plants to the customer electronically, they add the price and SKU for the plants they want, then send the electronic list back to you?

Do you have to keep a history of the plants that are ordered by a customer?

How are you updating the prices/SKU/plants selected?



Do you have A2K7 on a workstation?
Is SQL Server running on a server with Windows Server?

[jrmvt]

You've got it! We send our list of plants (all 2500) to the customer as an Excel file. They return it to us with a retail price and a SKU (if they use SKUs) listed for each plant. We then append that information to our PlantPrices table.

As they order plants throughout the year, we attach a sticker with their specific retail price and SKU to each pot. That way, they can begin selling the plants as soon as they arrive and don't have to wait for someone on their staff to price them.

Our Accounting and Inventory software uses the same SQL database and it keeps track of what the customers have ordered, so I don't need my program to do that.

I'm not sure I understand your "HOW are you updating..." question:

If you are asking how we record plants requested by the customer - Customer places an order. We enter it into our Accounting/Inventory software. Then my Access program can present the user with a list of orders outstanding. User chooses an order and the Access program will print stickers for each plant ordered with that specific customer's information. The printing part is working fine at the moment, though I know I will need to update it if I change table structure.

If you are asking how we update the customer's price and SKU info - I import the Excel file as a table. I then have an Append query add a record with the CustomerID and each PlantID to the main PlantPrices table. Then I run an Update query to pull the Retail price and SKU into the main PlantPrices table. If a customer has a correction for only a couple of plants, I have a form that allows the user to choose a customer and see a list of their prices and SKUs. They can scroll down to the specific plant and make the correction.

A2K7 is running on 7 desktops and SQL is running on a server (2 server setup with SBS2008)

Hope all of this info helps. Spring is closing in and I really need to get this figured out before busy season hits.

Thank you!

[ssanfu]

If you are asking how we update the customer's price and SKU info - I import the Excel file as a table. I then have an Append query add a record with the CustomerID and each PlantID to the main PlantPrices table. Then I run an Update query to pull the Retail price and SKU into the main PlantPrices table. If a customer has a correction for only a couple of plants, I have a form that allows the user to choose a customer and see a list of their prices and SKUs. They can scroll down to the specific plant and make the correction.

Yes, that is what I was looking for.

So you have a table for the plants available (no prices or SKU), a table for customers and a junction table:

Table: PlantCustomers ( is this the PlantPrices table?)
---------------------------
PlantID - Linked to the Plants table (is this a long int?)
CustomerID - Linked to the Customers table (is this a long int?)
RetailPrice
SKU

----------------------------
That is the structure I would use.

Does every customer sell every plant you provide? If they don't, do you still load each customer with *every* plant?

Even if you create a record for every plant for each customer, it doesn't seem like it would take very long adding/updating 4 fields.

How long does it take to add a new customer?
How long to update the SKU/prices for an existing customer?

[jrmvt]

Well, I feel like a fool.

First off - to answer your questions:
Yes, what you called PlantCustomers is what I call PlantPrices.
No, the 2 ID fields are TEXT. Since they come from tables in our accounting program, I have no control over their data type. Also, there is one more field at the start called PPID - autonumber and PK. (or so I thought)
Adding customer and plant IDs is quick. Updating the Prices and SKUs is what takes 5 minutes or so.

Was working on this last night. We are getting ready to start a new season, so we have new plant offerings and new pricing information from the customers. I ran an SQL statement to delete all of the records from the PlantPrices table so I could start entering the new information. Customer and plants added quickly. Prices and SKUs still took forever, and that was a small table (only 2500 records)!

So I wondered if there was a problem with the table. Made a copy of the structure and as I was looking at it to see if it copied OK, I realized there was no PK set. Don't know how the PK got removed. So I set the PPID field to PK and then created an Index for CustomerID and PlantID. Not sure if that was the best way to do the Index, but thought I'd give it a try.

Ran the queries and both of them flew. Updating Prices and SKUs now takes only 5 seconds or so. Granted I'm only up to about 80,000 records, so we'll see if it stays speedy as I close in on 400,000.

I really appreciate your input and sorry to have wasted your time. It's not easy admitting I missed something that simple. And I still can't figure out how the PK got lost.

Thank you!

[ssanfu]

Glad you found the problem...

I really appreciate your input and sorry to have wasted your time. It's not easy admitting I missed something that simple. And I still can't figure out how the PK got lost.

It wasn't a waste of my time; everyone has had an experience like this. And this will help other people in the future that might have a similar problem. Sometimes all it takes is someone else looking to help nudge you in the right direction.



I don't think the update time will increase since the query is always working with a subset of the total records; each customer has only ~2500 records in the "PlantPrices" table. It would be different if the query was updating of all the customers and the prices. Then, as more customers were added, the time to do the update would increase.