Hi Tedit, >>other elements too but it will be mainly a news portal site. What >>makes more sense - to create separate databases for each category >>and then build news and othertables under each db, or can I just >>use one db and build many tables under it, news tables for each
As James already said: >One database. As many tables as you need. Is the only probable correct answer. You do NOT want several databases providing the data for one site, except in a few specific situations. >> One database, and only a few tables. As I see it you only need two >> tables for the news portal, as you want it. One table for the >> categories, and one for the news and you connect it by the id That is what I would do as well. Even considering the answer of John: >Careful with this approach, if your news website is VERY busy and you >expect to add thousands of articles in a very short time, then you might >want to create a table for every category as table size will be your >enemy in a year or two There would still not be the slightest reason to use more than one database. Using more than one database unneccesarily complicates the joining of tables to compose the pages you want to show. ONE DATABASE only. Then decide based on the expectation of the site becoming very busy or not whether you want about two tables or many tables. In case of using many tables, I would still try to make a compromise: one table for categories, one general content table, containing just essential information of the newsitems (like title, data/time submitted, path to a picture, whatever, essentially the info needed to build the front page of the site), as then one simple join will still be able to show the front, indexing page that you want. When deciding to use many tables (one for each category) you need a well thought of naming scheme for the newsitem detail tables as well, where I would resist the temptation of using the category name, instead (or: as well) include the cat_ID in the table name. As you definitely want the table names to be unique. Personally I would call them 'ZZ_nn_subject', where the ZZ serves to keep all the detail tables alfabetically close (handy when trying to get a listing of tables or for backing up ZZ_*) at the end of any table listing, "subject" say the first six characters of the subject name [stripped of all non ASCII characters!] and "nn" the autonumber ID from the subjects tables. Keeping nn before subject again maintains a logical sorting order when doing maintenance (your site visitors won`t notice, after all, "what's in a name?"). Anyway... >As far as the following considerations below, which option is >better (forget the description in my previous question): > >A) One database with many tables B) About 10 databases with very few tables AAAAAAAAA The only doubt there may be is one db with two tables or one db with many tables. But again, one DB! Two databases is already too much for running a site off (only useful actually when the site has specific subsites, like a forum, a "latest news" page, user content, whatever, that can be considered completely separate). As soon as any joining of information is involved, one database only. >Compare for each criteria below please: > >Searching data less tables, easier searching. >Retrieving data less tables, easier to locate info. generally one query less to do the trick. but as John noted, large table overhead may cost more than gained avoiding the extra query. >Backup Easier with less tables. But more flexible and possibly faster with more tables, as you will be able to backup only a few. Anyway, don`t store images in BLOB fields in the table, it is a backup nightmare as the tables become HUGE. And jpg`s being virtually uncompressable, the backup archive will always be huge as well. I automated a call center and they record (almost) all calls. Had I opted for storing the calls (.wav archives) as data in BLOBS, the database would have crashed already. Instead I save them to disk. Every once in a while (every week) the manager takes a DVD, transfers 5 G of calls to that DVD, and in the call table the calls are marked as 'not on-line', and the field 'CD_Volume' is filled in. On the web-interface of the business managers can either see info of a call, plus a link to the sound archive, or a description of on which DVD to find the recording. Had all the data been stored in the table (or even in a separate table), not only the database probably would hgave crashed already, backing up the table would have become a nightmare as well as it would have to be distributed over many DVDs. >Security Guess no difference here. >Future expansions (as far as columns or tables) Not too much difference either. Either way you set up your solution, you should set it up in such a way that it can be expanded 'indefinitely' (one more reason to, if you use separate tables for each category, include the autonumber ID in the table name. That way you won`t run out of possibilities for tablenames). Marc
