SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: Database Setup

  1. #1
    SitePoint Evangelist
    Join Date
    Jul 2001
    Location
    Michigan, USA
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Setup

    Hello Fellow Site Pointers!

    I've recently taken on a personal project that I think has the potential to grow quite large. I have decided to program using Perl and to use the mySQL database for storing my data.

    My next biggest problem is setting up the database. Obviously, one of the points of using a database is to avoid having to process your own data. How far do you go with this?

    Basically, I need to know the cons of using many tables. This seems kind of extreme to me, but it seems easy. Here is the structure I was thinking about.

    We'll use the sample of a video game website to explain what I was thinking of doing. Let's say our site does reviews, past that, we do reviews on both publishers and on games. We also do previews, on games, and need to have the expansion of adding more.

    My intent was the following:

    There would be a content table. This content table would store the Overall ID of the article. Of all the other contents (reviews and previews in our sample), type of the content (review or preview), and then the id in the type table.

    Then, we would have a review table. This would store the id in the type table (this table), the overall id, the type of review (game or publisher), and then the id in the game or publisher table.

    In the game table, we would store the id in this table, the overall id, the id in this table, and then the information specific to a game review (ratings, text, whatever).

    In the Nascar Thunder table (the specific video game review table) there would be the comments on the game.

    Now, this is just one simple example, and already we have four tables to list a single thing. As I sit here and think, I would probably use the overall ID instead of assigning a new ID in each table to save a little space and allow to make sure we can always use that ID in queries.

    Is this the way I should go. With the method I am looking at, I see tons of tables that would be popping up left and right

    Any insight would be greately appreciated! Thanks for your time!

  2. #2
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think that I see what you're saying, so here's a shot at it:

    Previews:
    preview_id,
    game_id,
    preview_text

    Reviews:
    review_id,
    review_type_id, (whether it's a game or publisher)
    review_subject_id (the id of the game or publisher),
    review_text

    Games:
    game_id,
    name,
    description,
    publisher_id
    ... (any other info about the game)

    Publisher:
    publisher_id,
    name,
    description,
    ... (any other info about the publisher)

    Review_Types:
    review_type_id,
    name ('game', 'publisher'. this gives you room to add more types later without a problem)

    User_Comments:
    comment_id,
    comment_text,
    comment_subject_id,
    review_type_id (game or publisher comments)

    I think that should be it. This way, you don't have to create a new table for every game. You can simply have a row in the "games" table and then it relates to the rows in the "reviews" and "previews" tables. When users add comments, those comments are related to the base tables, as well.

  3. #3
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, this would work I think, but maybe add column game_id to the reviews and preview tables, so you can see what game they're referreing to?

    What about this:

    Code:
    table_game
    ---------------
    game_id                 integer, PK
    name
    description
    rating
    ........
    
    table_game_texts
    game_text_id          integer, PK
    game_id

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, my browser went wild and sent the last post too early
    What I wanted to add was the schema:

    Code:
    table_game
    ---------------
    game_id                 integer, PK
    name
    description
    rating
    ........
    
    table_game_texts
    ---------------
    game_text_id          integer, PK
    game_id                 integer, FK, table_game
    text
    text_type_id           integer, FK, table_text_type
    entry_date          
    sort_order
    
    table_text_types
    ---------------
    text_type_id           integer, pk
    text_type_description

    Then you can store all of your reviews, previews, comments, and whatever else text types in one table. Each row in table_game_texts has been assigned a text type and is assigned to a game. Only 3 tables is an improvement of 25%

  5. #5
    SitePoint Evangelist
    Join Date
    Jul 2001
    Location
    Michigan, USA
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Richard - The problem is that the different contents will have different field, so putting them all in one database isn't possible. You also mention an improvement of 25%. That's getting more at what I was looking for. Is it costly on the server to add more tables, or do more tables just mean more data.

    Dave - haha, thanks for pointing that out. Don't know why I didn't think of having all the comments in a single table. I mean, then they can just be accessed by a simple select. Thanks for point that out. I guess I was thinking of assigning the review to the comments instead of the comments to the review...definitely not the way to go

    Thanks a lot for the help guys!

  6. #6
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:

    Any particular reason why you chose Perl over PHP? I just think you might regret that choice down the road if this project is going to be as big as you say it is....but it's your choice. Just curious.
    Aaron Brazell
    Technosailor



  7. #7
    SitePoint Evangelist
    Join Date
    Jul 2001
    Location
    Michigan, USA
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sketch - no need to start the Perl vs PHP debate

    At any rate, I am choosing Perl because I am currently a much better programmer in it. I lack the object oriented programming skills to script as well as I'd like in PHP. Running Perl as an Apache module (via mod_perl) puts Perl in the same grouping as PHP anyway. Oh, and *points at www.slashdot.org*


  8. #8
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Object Oriented skills? Someone forgot to tell me about that.

    At any rate, what am I looking at on Slashdot? I'm a bit lost.
    Aaron Brazell
    Technosailor



  9. #9
    SitePoint Evangelist
    Join Date
    Jul 2001
    Location
    Michigan, USA
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    haha, I was just saying that Slashdot is a HUGE site that gets a large amount of traffic, that is run on a Perl driven solution.

    Object oriented programming brings the focus onto the data, which is the most important part Mainly, I just feel more comfortable with Perl in a project of this because of my experience with it. I am somewhat of a newbie to PHP (sub six months) so my PHP is very Perl'ish at this point anyway. I don't know proper OO techniques with it and basically just need to put some more time into learning it (which I am definitely doing. Recently, finished my quick read through of Programming PHP and started Web Database Applications with PHP and MySQL, and will go through both a little more thoroughly when I finish).

    If I had more knowledge of PHP, I probably would have gone with it

  10. #10
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Andy Tomaka
    Richard - The problem is that the different contents will have different field, so putting them all in one database isn't possible. You also mention an improvement of 25%. That's getting more at what I was looking for. Is it costly on the server to add more tables, or do more tables just mean more data.
    Thanks a lot for the help guys!
    Hello again,

    Having 3 instead of 4 tables is a reduction of 25% is all I meant. Since you wanted fewer tables I called it an improvement .

    Well, the more tables you have, then the more normalized your data probably is. This is likely to increase the usefulness of your data, but not necessarily so. If you have fewer tables, then you have fewer relationships and this will definitely making coding your application easier.

    More tables do not necessarily mean more data, it is not costly in any way. Actually, if your data is normalized appropriately into several tables, you are likeley to reduce data redundancy and reduce the total amount of data. With the side benefit that your app may be faster.

    Its always a bit of give and take how normalized your data is. Keep it as denormalized as sensible is my view.

    I would agree with the other posters here. PHP is the way to go for your web site, objects are not particularly data - oriented, and if you don't want to you don't have to use classes in PHP at all. PERL is IMHO definitely more of a text-manipulation thing with CGI + DB stuff bolted on. PHP does these things natively, and does them very well.

    Regards.
    Richard

  11. #11
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, PHP does not have to be OO, it just happens to be one of the very versatile things it can do. Besides PHP is very Perlish by nature. After all, PHP's grandaddy was Perl.

    Aaron
    Aaron Brazell
    Technosailor



  12. #12
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cutting back by one table isn'y going to make a huge difference when it comes to performance or coding, though. Tables, are cheap and easy to use...

  13. #13
    SitePoint Evangelist
    Join Date
    Jul 2001
    Location
    Michigan, USA
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand that PHP does not have to be Object Oriented, but I'm saying that this project needs to be object oriented styled. It makes it much easier to add in functions to deal with the data. Using an OO style gives me the oppurtunity of rewriting the system into any language with ease.

    objects are not particularly data - oriented,
    That's the reasoning behind object oriented programming. The following quote comes from O'reilly's Advanced Perl Programming. Yes, it's a Perl book, no, Object Oriented concepts do not change from language to language.

    Complex systems are inherently hierarchical, and many abstractions and methodologies have been invented to take advantage of this aspect. Until the late seventies, functional decomposition (top-down design) held sway as the definitive method to understand and implement complex systems. A developer would begin by writing high-level pseudo-code and continue refining each part of it until it was detailed enough to be translated into an implementation language. Nicklaus <A class=indexterm name=ch07-idx-969709-0>Wirth called this approach stepwise refinement. Then came structured methodologies, SA/SD (structured analysis/structured design) chief among them, which employed many tools and notations such as data-flow diagrams, process specifications, data dictionaries, state transition diagrams, and entity-relationship diagrams to design, document, and develop a system. The accent continued to be on the procedural side of systems development rather than on the dynamic (state transitions) or structural (data) facets.
    The key realization in the last 15 years or so has been that a system's functionality tends to change a lot more than the data on which it acts. A personnel information system keeping track of employee details soon knows as much about an employee as it ever will. On the other hand, its functionality tends to track management reshuffles, tax laws, medical insurance changes, and the noisy arrivals and silent departures of Directors of Human Resources.
    This realization has completely inverted the way a problem is tackled now. Data and ways of structuring it are now given primary importance, and code is organized in modules around important pieces of data. The benefits are immense and immediate
    Feel free to look around a bit and you'll find that the main importance of Object Oriented Programming is in fact that it gives more emphasis to the data instead of the functions.

    I have researched both Perl and PHP options. If it comes down to it (and I highly doubt it will), I will be able to recode my solution with ease in any object oriented language. All I lose is the time it takes to recode which is negligible compared to the actual data.

    The language debate can go on forever, it's why I asked that we stay away from it I have done my research and Perl was not chosen on a whim.

    Thanks for your help all!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •