two sites and a point of sale interface in one database

msql inventory for two stores in one database

[cdcaveman]

We currenlty have one site selling blank decks. We are putting the entired store on another site. We want to have a point of sale interface to our application , a shopping cart for the retail store, and a shopping cart for our other site that sells the blank decks. it sould seem that having one database would be much easier as far as avoiding the constant reconsiliation of inventory through manuel input or some xml feed. Does this sound like a reasonable solution? We are using a shopping cart that would have to alter in both instances on the retail store, and on the blanks site to accomidate some website specific user type to be able to differentiate the pricing and products to be displayed on one or the other site. i'm just a bit confused on how to handle this inventory problem. i don't wanna have things in my store be sold and still showing up on the webiste and vice versa. is there anyone with any ideas or questions about what i'm asking

[Ahhk]

The easiest, and most dangerous way, would be to just setup the second server to connect to the database on the primary server remotely (instead of a local connection). This being dangerous because you would obviously need to allow remote access to the db. Granting access to the database only to the second server's IP would help a little in that respect, though.

The better, much more complicated way would be to make web-service calls from the second server to the primary server for all database functions. You could use SOAP, XML-RPC, REST, or any of the others. You wouldn't need to allow remote access and could authenticate (and filter out SQL injection attempts in) each call.

[edited by: Ahhk at 9:01 pm (utc) on Sep. 17, 2008]

[cdcaveman]

hmmm, i think to sort of drive down what i'm attempting to do. i'll say this. we have products from two online busiensses that we wanna selll we don't wanna physically go move there inventories from one place to another. XML is an answer for sure. but why would we not just have a db that is sort of a warehouse for inventory for both businesses? then there would been on updating feed

[Ahhk]

Ummm, having only ONE database for both sites is exactly what I'm talking about.

[cdcaveman]

i posted this same thing on another thread here it is

well , i'm looking for a solution. i have two stores. one is retail lets just call it pd. it sells products through a brick and mortar location and in the near future a shopping cart application. There is also a site lets just call it blankdealer that is a wholesale platform that offers retail to the public as well if you can't supply a tax certificate. so now that i've set the stage. there are products in the brick and morter store that will be sold on the pd site. there are products that will be sold on the pd site that come from the blankdealer site. There is point of sale software in the store that has its database. there is going to be a database for the pd site. and there is going to be a database for the blankdealer site. So what i'm thinkign is using one database , creating a web application interface for the brick and mortar store (esentially a pos on the web) the shopping cart for that pd store. Adn then at that point use ONE Database to hold all the inventory. so that if i sell a blank on the pd site then its gone, i can't sell it in the store on the point of sale app. or on the pd site online. is ONE database the answer. or is some xml feed application better? i'm so new to this arena period i'm confused.

[cdcaveman]

so why would you use xml or anything there of if you had one database. what my partner is sort of explaing to me is that we will have to change ever instance there is a query or mysql statment to accomidate the change in database structure , which would be obvious. but i'm thinking the time taking to change code and create one inventory db would be saved over time by having inventory vitually avaiable on the three platforms (pos,2shoppingcarts) . this way not creating any potiential back order frustrations. or need for manuel imput of changes

[cdcaveman]

oh we use a hosting company, they have a database server, and our hosting server. i guess thats what you were talking about as seperating the two? db serv,hosting serv

[Ahhk]

Of course you want to have the inventory in one database/table that is accessible to both sites.

You could even have separate pricing for each site by having a pricing column for each one.

ex. id, product, sku, qty, pd_price, blank_price, etc....

The pd site would grab whatever data was need and the pd_price, and the other would do the same but with the blank_price

What I was originally talking about was based on the assumption that the two sites were on different servers - and sharing one database. If they are on the same server, then its a non-issue and pretty simple to build.

[cdcaveman]

well, we currently have our shopping cart arrangement done in such a manner that it would i guess take a considerable amount of work to change all the instinces in which these sql statments were using these new tables added. I would really like to create a Coldfusion component that handles the queries, then one to handle the actions, meaning updates and deletes. i'm thinking that in this way. i can just refer to the cfc at every current instance and then just make the cfc handle the new database structure. We are using Cartweaver right now. we have done some adjustments to it. making wholesale users able to have different pricing through a registration process. i really like the way its turned out. i want to possible use one administration site for both sites. but i'm not sure yet waht that implys.