Results: How should i deploy this app ? 

Voters
3 You may not vote on this poll

  • 1 Db per client and templates in DB
    0%
  • 1 Db per client and templates NOT in DB
    33.33%
  • 1 Db for ALL clients and templates in DB
    33.33%
  • 1 DB for ALL clients and templates NOT in DB
    33.33%
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    UK
    Posts
    76
    Rep Power
    13

    1 db per client or 1 db for ALL clients


    Having searched the forums i couldnt find a conclusive answer so im starting my own thread.

    Im currently developing an application with a team of 3 others, which is an inventory/stock control (As if there arent enough already) system with some industry specific features. (its for a PC shop)

    Its being developed in PHP and PostgreSQL.
    At the moment we're experimenting with smarty, but we may base the application on phpgroupware and ck-ledger.

    What we plan to do is, rent the application to other local pc shops in the area after it has been completed and tested.

    A certain requirement for the appplication is that we need a central way of managing all the clients
    i.e how many people are using the application,
    who has paid, who hasnt etc etc.
    We also want to be able to provide them the option of being able to edit their own interface design (ie edit the smarty templates)

    Now, My personal view was that we should deploy the application in such a way, that each client is given his own postgreSQL database, and the templates are also stored in the RDBMS.

    Advantages with this are ; its easier to setup, as ur repeating the same action for each client.
    Templates should be easier to manage if they are in the RDBMS

    Weaknesses however are ; Its alot of administration work and storing the templates in the database may cause unneccassary load.

    MY programming team suggest using the file based approach creating a php script that writes directly to file.

    They also suggest have ONE database for ALL clients, where each client has a unique ID for all his data entry


    Any experts out there who can give some guidance?
    What would be the best route to take in ur opinion ?

    By the way, if anyone is wanting to get involved in this project if they have some spare time. please feel free.

    Unfortunately we are unable to pay as we are a very new company and at this stage have NO clients.
    However i DO promise an excellent reference on ur CV/Resume and a chance to gain some commercial experience.

    Also, any GUI designers out there who could help us create an excellent interface design ?
  2. #2
  3. cosmos curator
    Devshed Novice (500 - 999 posts)

    Join Date
    Mar 2002
    Location
    Leeds, UK
    Posts
    678
    Rep Power
    14
    In my experience I've found that smarty can be a little slow and a pain to implement. That is, in my experience.

    (I say in my experience because it seems that some people on this board have a hard time believing that others can have experiences different to theirs and can end up on a very high horse begining their own crusade. Usually its the hard-core techies that don't have a life other than reviewing the W3C standards and only wear t-shirts that say "Linux till I die". )

    I use the ultratemplate/bTemplate engine, because they're extreamily configurable, and with a little PHP knowledge quite extensable. Plus, you can eliminate sections you know you won't be using without much reworking.

    I've worked mainly on CMS (Content management systems) since I got into the industry, in various different circumstances and languages. Things you have to take into consideration include:
    • How many companies will be using this service?
    • Will each company have their own site, or will they be sub-domains of a main site?
    • How much will the sites be hit?
    • How much will each company be storing?
    • What sort of query-level cashe support does your database have? (I know that MySQL 4+ handles caching of regular queries where the results haven't altered, to speed up query times)
    • What sort of technical knowledge do these companies have? (I believe your application is for PC Shops, but how much do they know about HTML design?)
    • What will the users be using to view the sites once completed?
    • How much do you trust the companies hosted? (Who says they won't run malicious code?)

    These questions can greatly influence the design of your application.

    Should you need any further assistance, please do not hesitate to email me via the board!
    Last edited by neobuddah; November 11th, 2002 at 09:07 AM.
    R.T.F.M - Its the only way to fly...

    "No matter what you do, or how good it is, someone will always ask for more features. Or to change the colour of something, then change their minds."

    Personal:
    experience// 8 Years Web Development
    technologies// Standards-compliant, valid, & accessible (x)HTML/CSS, XML/XSL/XPath/XQuery/XUpdate, (OOP) PHP/(My)SQL, eXist/Xindice/XMLDBs
    packages// Photoshop, Illustrator, Flash/Fireworks/Director
    environment// FC2, MySQL, Lighttpd, PHP5, Mojavi/Agavi
    site// //refactored.net/ (Coming soon...)
    quote// Programming is the eternal competition between programmers who try to make apps more and more idiot proof and the universe that makes dumber idiots. So far, the universe is winning...
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    UK
    Posts
    76
    Rep Power
    13
    Thanks for your advice, i appreciate the sincere advice
    In answer to your questions : -


    How many companies will be using this service ?
    Well for this project, i dont envisage more than 5 in the first year, and maybe 20 or 30 18months down the line.

    How much will the sites be hit ?
    Well its an application coming over the web, so i presume the clients will be accessing it all day during working hours, at least 5 days a week.

    Each company will be storing at least approx 500 lines of stock with their customer database of around 500 customers. I dont know how that would translate into megabytes. They're likely to be doing about 50-100 transactions a day (sales invoice/purchase orders etc)

    Im using postgreSQL, im not sure what kind of query cacheing support it has (i'll find out)

    These companies are not expected to know the html.
    we'd like to give them a simple interface that allows them to change colour of header etc etc. Not revamp the whole layout.

    The users will be using Internet Explorer (unless u can suggest an alternative ? since i want to setup a VPN)

    How much do i trust the companies hosted ?
    hmmm.....not sure about this one. Guess its better to accommodate for the worst case scenario.
  6. #4
  7. cosmos curator
    Devshed Novice (500 - 999 posts)

    Join Date
    Mar 2002
    Location
    Leeds, UK
    Posts
    678
    Rep Power
    14
    How many companies will be using this service ?
    Well for this project, i dont envisage more than 5 in the first year, and maybe 20 or 30 18months down the line.
    Ok, then firstly I'd say a single database for all clients.

    How much will the sites be hit ?
    Well its an application coming over the web, so i presume the clients will be accessing it all day during working hours, at least 5 days a week.
    Ah... I'd suggest using MySQL then, and definatly use seperate databases. MySQL is designed to be extreamily fast (especially MySQL4+. I've heard its a contender for Orancle, but don't quote me on that), which is always a good thing.

    Each company will be storing at least approx 500 lines of stock with their customer database of around 500 customers. I dont know how that would translate into megabytes. They're likely to be doing about 50-100 transactions a day (sales invoice/purchase orders etc)

    Im using postgreSQL, im not sure what kind of query cacheing support it has (i'll find out)
    While you're at it, compare it with MySQL. They integrate very well, and seem to be standard on most linux boxes. Can I ask why you're using postgreSQL?

    These companies are not expected to know the html.
    we'd like to give them a simple interface that allows them to change colour of header etc etc. Not revamp the whole layout.
    Store standard templates as files, and allow the user to update a flat file or single row in a database, but test the overhead on both of these to see which is fastest and requires less memory.

    The users will be using Internet Explorer (unless u can suggest an alternative ? since i want to setup a VPN)
    Personally, i'd suggest IE6. I use this for all my online applications. Its very forgiving when it comes to markup, which can allow for quite a few workarounds that can't be done in IE4+/NS/Moz. (Oh god, wait for it, some techie's bound to have a go now... )

    How much do i trust the companies hosted ?
    hmmm.....not sure about this one. Guess its better to accommodate for the worst case scenario.
    If you're not allowing them up upload HTML information, or PHP information, then its ok. Make sure you close your code as best you can. See the sticky in the PHP forum on security.
    R.T.F.M - Its the only way to fly...

    "No matter what you do, or how good it is, someone will always ask for more features. Or to change the colour of something, then change their minds."

    Personal:
    experience// 8 Years Web Development
    technologies// Standards-compliant, valid, & accessible (x)HTML/CSS, XML/XSL/XPath/XQuery/XUpdate, (OOP) PHP/(My)SQL, eXist/Xindice/XMLDBs
    packages// Photoshop, Illustrator, Flash/Fireworks/Director
    environment// FC2, MySQL, Lighttpd, PHP5, Mojavi/Agavi
    site// //refactored.net/ (Coming soon...)
    quote// Programming is the eternal competition between programmers who try to make apps more and more idiot proof and the universe that makes dumber idiots. So far, the universe is winning...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    UK
    Posts
    76
    Rep Power
    13
    While you're at it, compare it with MySQL. They integrate very well,
    well i went through quite a few days of research and did quite a bit of reading. After asking many questions i decided postgreSQL would be better for it has foreign keys, transaction and stored procedures support.

    Store standard templates as files, and allow the user to update a flat
    Good idea ! yes its sort of a nice compromise between the two and probably an optimal solution
  10. #6
  11. cosmos curator
    Devshed Novice (500 - 999 posts)

    Join Date
    Mar 2002
    Location
    Leeds, UK
    Posts
    678
    Rep Power
    14
    MySQL 3 can handle transactions too. Not too sure about the foreign keys though, or stored proceedures. As I've mentioned, check out MySQL 4.0.4, I believe that version has addressed such issues.

    When I mentioned a flat file, try storing the configuration information as constants. This is a very secure way to store things. Your file might look like:

    Code:
    define("ENV_CSS_FILE", "root/css/designer.css", TRUE);
    define("ENV_COMPANY_TITLE", "My Company.Inc", TRUE);
    define("ENV_HEAD_DESCRIPTION", "My Company.Inc ensure the best quality PC hardware on the planet...", TRUE);
    ...
    Then include this file in the client's scripts. That way, no-one can run malicious code from the address bar:

    Code:
    -- file --
    $env_css_file = "root/css/designer.css";
    -- /file --
    
    -- address bar --
    index.php?env_css_file=;eval("www.somewhere.com/my_malicious_script.php");
    -- /address bar --
    R.T.F.M - Its the only way to fly...

    "No matter what you do, or how good it is, someone will always ask for more features. Or to change the colour of something, then change their minds."

    Personal:
    experience// 8 Years Web Development
    technologies// Standards-compliant, valid, & accessible (x)HTML/CSS, XML/XSL/XPath/XQuery/XUpdate, (OOP) PHP/(My)SQL, eXist/Xindice/XMLDBs
    packages// Photoshop, Illustrator, Flash/Fireworks/Director
    environment// FC2, MySQL, Lighttpd, PHP5, Mojavi/Agavi
    site// //refactored.net/ (Coming soon...)
    quote// Programming is the eternal competition between programmers who try to make apps more and more idiot proof and the universe that makes dumber idiots. So far, the universe is winning...
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    UK
    Posts
    76
    Rep Power
    13
    im going to try that


    Thank you
  14. #8
  15. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Hehe, neobuddah has some good advice about constants, etc... but he and I have also had our arguments over PostgreSQL in the past . (MySQL is not going to be any kind of contender for Oracle anytime soon, but you already know that .)

    Anyway, on to our problem at hand:

    One thing you should note is that PostgreSQL will soon have full DOMAIN support. (see documentation)

    I know this might be adding more headache for you to think about, but domains make it easier to abstract many things about a single database, to essentially make it "look" different to different users. I believe if implemented properly, you can make it seem as if each user (client) has its own database. But as each defines tables and types for its domain, they can all be members of the same database, allowing you (the root user) to get statistics and make changes on the whole database.

    I know this is not yet out of beta for PostgreSQL, but it is one of the most important features they are pursuing. This will make PostgreSQL only 1 or 2 steps away from comparing to Oracle or DB2, which have full DOMAIN support.

    Other than this, yes, I tend to prefer keeping track of multiple users in one database to keeping track of one user in many databases.

    As to the template question: I personally think of templating systems the way I think of government ("That government governs best which governs least." --Thoreau). Meaning, that template system which has the simplest features is probably best. The argument against Smarty is that it is almost a whole bloated language unto itself. PHP already has all the features you need to handle logic, so a template class should really be just about the basics of sucking in static content and replacing markers. At most, I think it should also allow for multiple nested blocks inside each template file (so you can loop several times through block B inside of block A). All the rest is needless fluff.

    As to storing templates inside the database, this question is similar to the question of storing binary image data in a table. It is a performance hit, but if your data integrity is very important, then it might be worthwile, because you can define integrity constraints on a table, which you can't on the filesystem. This can make sure that files are not accidentally deleted or somesuch. But generally, I only recommend database storage if you really need data integrity, or advanced ability to manipulate your data. It could provide some interesting logical benefits, if used carefully.

    Of course, also the performance hit of templates in the database is not that big a deal if your template system does some sort of caching. Ideally, if you have enough RAM, it would be great to cache template output into shared memory, with PHP's shmop functions.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  16. #9
  17. cosmos curator
    Devshed Novice (500 - 999 posts)

    Join Date
    Mar 2002
    Location
    Leeds, UK
    Posts
    678
    Rep Power
    14
    At most, I think it should also allow for multiple nested blocks inside each template file (so you can loop several times through block B inside of block A). All the rest is needless fluff.
    Definatly look at the bTemplate/Ultratemplate engine.

    but he and I have also had our arguments over PostgreSQL in the past
    Lol, you can say that again! Muchos respect to you rycamor. I've noted some benchmarks for all the major db engines recently, including oracle and mysql4, i'll try to find the link and post it, but I remember seeing oracle and mysql4 battling it out at the top, with sqlserver/sybase/blah blah blah all fighting with themselves somewhere in the background. As for PostgreSQL, I'm did a lot of research a while back, and after much consideration plumped for MySQL... although can't remember why. However, I'm the type to always make an informed decision if its to be pretty much final.

    Its a pitty you can't assign something like session constants - that are held within the session vars for the duration of the stay but cannot be re-assigned. Then you could hold company-specific values in a db table row, pull them out at the start of the session, push them to session constants, then refer to them rather than re-open a file or query the db on every request reducing overheads.

    I'll look into possible security concious solutions to reduce overheads for that type of situation. Rycamor, if you know of anything, or have any ideas, mail me direct. Maybe we could compile a solution and share it.

    Just a little note, I would change the poll and add another item:

    Choose db design per application.

    There's no be-all-and-end-all for web app design.
    Last edited by neobuddah; November 12th, 2002 at 04:35 AM.
    R.T.F.M - Its the only way to fly...

    "No matter what you do, or how good it is, someone will always ask for more features. Or to change the colour of something, then change their minds."

    Personal:
    experience// 8 Years Web Development
    technologies// Standards-compliant, valid, & accessible (x)HTML/CSS, XML/XSL/XPath/XQuery/XUpdate, (OOP) PHP/(My)SQL, eXist/Xindice/XMLDBs
    packages// Photoshop, Illustrator, Flash/Fireworks/Director
    environment// FC2, MySQL, Lighttpd, PHP5, Mojavi/Agavi
    site// //refactored.net/ (Coming soon...)
    quote// Programming is the eternal competition between programmers who try to make apps more and more idiot proof and the universe that makes dumber idiots. So far, the universe is winning...
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    UK
    Posts
    76
    Rep Power
    13
    thank you for the detailed answers. this helping me loads, i should be able to make a more informed decision now.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    UK
    Posts
    76
    Rep Power
    13
    Just wondering

    how would u personally set the database up so that the data in the tables belongs to the individual client ?

    I have 20 tables in there already, and i was thinking of just referencing each table with " customerID" as a foreign key.

    is that going to be good enough or would you do something else ?
  22. #12
  23. cosmos curator
    Devshed Novice (500 - 999 posts)

    Join Date
    Mar 2002
    Location
    Leeds, UK
    Posts
    678
    Rep Power
    14
    That should be fine. Check out some database normalisation tutorials (try devshed and PHPBuilder.com). These be a great resource.
    R.T.F.M - Its the only way to fly...

    "No matter what you do, or how good it is, someone will always ask for more features. Or to change the colour of something, then change their minds."

    Personal:
    experience// 8 Years Web Development
    technologies// Standards-compliant, valid, & accessible (x)HTML/CSS, XML/XSL/XPath/XQuery/XUpdate, (OOP) PHP/(My)SQL, eXist/Xindice/XMLDBs
    packages// Photoshop, Illustrator, Flash/Fireworks/Director
    environment// FC2, MySQL, Lighttpd, PHP5, Mojavi/Agavi
    site// //refactored.net/ (Coming soon...)
    quote// Programming is the eternal competition between programmers who try to make apps more and more idiot proof and the universe that makes dumber idiots. So far, the universe is winning...
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    UK
    Posts
    76
    Rep Power
    13
    well i've done a BSc in software engineering , so i "should" know how to normalise....lol

    well my development partner certainly does anyway,

    I better refer back to my university notes...haha
  26. #14
  27. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Posts
    8
    Rep Power
    0
    I started designing a CMS with the same components (PostgreSQL and Smarty) and I had the same throughts about the database and the templates.

    I decided that every website (=client) will have it's own database.
    I think this is more secure, we all make mistakes (programmer, admin, user) so when all data is in one database none of your pages work but if you have different databases only the one you are connected is crashed.

    Regarding the templates I decided to store them in the database because then they are easier to manage.

    - dom

IMN logo majestic logo threadwatch logo seochat tools logo