Database HELPPPPPP

D

Dave33445

New member
May 4, 2007
139
1
0
I am in over my head on a project and need some brainstorming assistance from anyone who has a minute...

I have to build a php application that does estimating for contractors.

Each company that uses this system will start with 8,000 unique products that each company has unique pricing on. From there, the companies will each and and remove more products (in the thousands - 10s of thousands).

Is it better to create a new products table for each user (50-100 users)

Or do I create one massive table that has all the products?

Do I store the product info in XML and pull it in that way?

I havent worked with this much data and don't know where to start on the data structure.

Any recommendations would be awesome.

Thanks in advance.
 


I'd do it like this:


products: product_code, desc
companies: company_id, name, contact_info
prices: company_id, product_code, price

You could put more fields in as needed, just keep the keys where they are.
 
one big table with proper indexes is fine, not one table per customer. look up normalising data, if you dont know it.
 
Thank you for the quick replies. Let me ask you one more question.

When a new user is added, since all this data needs to be replicated (8000 records for the pricing) what is the best way to do this in a browser. These aren't technical people that can run the scripts on the comand line.

Thanks again
 
Dave33445 said:
Thank you for the quick replies. Let me ask you one more question.

When a new user is added, since all this data needs to be replicated (8000 records for the pricing) what is the best way to do this in a browser. These aren't technical people that can run the scripts on the comand line.

Thanks again
Click to expand...

Triggers executed natively in the database or a SQL command executed server side. If you really mean "replication" then the customer won't be editing the data before insert and there is no reason to ship it client side in the browser.
 
Dave33445 said:
all this data needs to be replicated (8000 records for the pricing)
Click to expand...

This just doesn't sound right at all!

As already mentioned, you only need product+company association in a price table.

No need to replicate the products as many times as the number of the companies.
 
jryan21 said:
I'd do it like this:


products: product_code, desc
companies: company_id, name, contact_info
prices: company_id, product_code, price

You could put more fields in as needed, just keep the keys where they are.
Click to expand...

I'd favour similar to what is above, although i'd actually add auto incrementing primary ids to each table, but that's just me. I'd then, using the price table like a lookup table, access all as their primary ids and create an id column in the prices table made up of product.id and companies.id.
 
Let me clarify a bit.

Let's just say for examples sake I am a book wholesaler with 8000 books in inventory.

I sign up amazon, and amazon has 8000 unique prices to input.

Then I add Borders and they input their 8000 prices

and on and on

By replicate I certainly didnt mean database replication, i meant copy the 8000 default books and paste them in with the borders company id, and with the amazon id, etc

You also advised now to have the client do anything, but thats impossible. The tool is for a client. He then gets clients (amazon, borders) and will need to add them. When they are added the execution of the script to add the 8000 entries would occur. That would have to be browser based given his skill level.

Does this make any more sense now? Books are just the example, its actually going to be manufacturing components and realistically its going to be a lot more then 8000. The sample data he gave me to work with was 8000 items.

Thanks again, you guys are a huge help!
 
Dave33445 said:
Let me clarify a bit.

Let's just say for examples sake I am a book wholesaler with 8000 books in inventory.

I sign up amazon, and amazon has 8000 unique prices to input.

Then I add Borders and they input their 8000 prices

and on and on

By replicate I certainly didnt mean database replication, i meant copy the 8000 default books and paste them in with the borders company id, and with the amazon id, etc

You also advised now to have the client do anything, but thats impossible. The tool is for a client. He then gets clients (amazon, borders) and will need to add them. When they are added the execution of the script to add the 8000 entries would occur. That would have to be browser based given his skill level.

Does this make any more sense now? Books are just the example, its actually going to be manufacturing components and realistically its going to be a lot more then 8000. The sample data he gave me to work with was 8000 items.

Thanks again, you guys are a huge help!
Click to expand...

jryan21 answered this question with a prices table:

"prices: company_id, product_code, price"
 
Dave33445 said:
Let me clarify a bit.

Let's just say for examples sake I am a book wholesaler with 8000 books in inventory.

I sign up amazon, and amazon has 8000 unique prices to input.

Then I add Borders and they input their 8000 prices

and on and on

By replicate I certainly didnt mean database replication, i meant copy the 8000 default books and paste them in with the borders company id, and with the amazon id, etc

You also advised now to have the client do anything, but thats impossible. The tool is for a client. He then gets clients (amazon, borders) and will need to add them. When they are added the execution of the script to add the 8000 entries would occur. That would have to be browser based given his skill level.

Does this make any more sense now? Books are just the example, its actually going to be manufacturing components and realistically its going to be a lot more then 8000. The sample data he gave me to work with was 8000 items.

Thanks again, you guys are a huge help!
Click to expand...

Do you have any experience in Relational Database Management Systems?

Because if you don't, this will be a tough job, especially if you've never wrote php/html pages to interact with the tables in the database.

Undoubtedly though, as already said, you need at least 3 tables. One will list all the products, give each product in the table a unique identifying id and any other info relating to just the product (p&p, size, name, description, etc., etc.).

Another table will list all the clients/companies. This will be like above. One column with a unique id, and other columns detailing client info (name, address, phone, email, etc., etc.).

The last table (prices) acts as a connection between the above 2 tables. You will have an id column, which identifys any particluar row. Then you have another with the id relating to the client/company from the clients table, another specifying the id from the product table. You'd then also have another column holding the price of said product.

Essentially, what this will means is that you a table with products, a table with clients and a table with prices that points at both a product and a client for any given row and contains the price.

I not trying to be patronising, but i'm unsure as to your knowledge. ;)

Also, to add a client and for them to edit the proces, you need to write html/php pages that query the DB and edit the tables accordingly.
 
I understand databasing and have been doing PHP for a long time. I just don't have experience with tables that have several million records. I am concerned how to keep this thing under control in such a mass data scenario which is why I originally was thinking XML feeds per client might be a more feasible option. New customer, just copy the original xml and they can then edit their pricing add or remove products from their feed. PHP 5 has a lot of badass tools for working with xml I just wasnt sure which way was more efficient in this type of situation.

I still don't know how to add 8000+ records on the fly in the browser which is why I keep circling back to xml vs db.
 
You're not getting it.
Quit thinking about using the browser to import things into the database.
Browsers are for editing one (or a few) records at a time via a pretty gui interface known as your website. There are other tools for importing data into mySQL.
 
If it was my site that would be fine. The individual I am building this for is not technical. He needs a point and click interface through his website for creating new users. With each new users then 8000+ records need to be assigned for that new user.

Does that make sense?
 
Dave33445 said:
If it was my site that would be fine. The individual I am building this for is not technical. He needs a point and click interface through his website for creating new users. With each new users then 8000+ records need to be assigned for that new user.

Does that make sense?
Click to expand...

Yes but you don't need to 'create' 8,000 new records for that new user. The records already exist in the products table.
If the new user registers on the site and they want to add their prices to their product, then they will search for said product, click a little button that says "add your pricing to this product", they enter their price, hit save, and now your script writes another row of data to the "Prices" table:

INSERT INTO prices (company_id, product_code, price) VALUES ('1234', '7885', '20.00')
 
Wrong, its estimating software for manufacturing. Every company needs an entry for every product. The company will have the ability to add and remove additional products, but every product needs to be there from the start with a default price.

That is where the issue is coming in. Do you not think it would be more efficient to use XML feeds?
 
I wouldn't add 8000 records from the browser. Probably the quickest way is to write the insert statements into a txt file, save it as .sql, then import it through comand line or phpmyadmin.

I've never worked with xml and php in this manner, so couldn't advise you for comparisons sake, but it's what databases were designed for and if you wanna create xml feeds, then you can build them on the fly using the info held in the DB.
 
adding and removing products via xml or mysql is not the issue as thats obvioulsy a very simplistic task.

the issue is getting the initial data in. as -God- said, you can't add 8000 records via browser.

I am thinking maybe a curl script that builds the data and runs every night and picks up any new users, then I could just tell my client a new user takes 24 hours to get fully set up??
 
You must log in or register to reply here.
Top Bottom
Back