Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11
  1. #1
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Anything like that exist? I sort of need to extend a DB table under certain circumstances. Kind of hard to explain.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    You can model your DB for OO, like so:

    Code:
    CREATE TABLE person (
        person_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(255) NULL,
        last_name VARCHAR(255) NULL
    ) TYPE-InnoDB;
    
    CREATE TABLE user (
        user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        person_id INT NOT NULL REFERENCES person(person_id),
        username VARCHAR(255) NOT NULL,
        password VARCHAR(255) NOT NULL
    ) TYPE-InnoDB;
    Then, your classes can look like this:

    PHP Code:
    class Person {
        protected 
    person_id;
        protected 
    first_name;
        protected 
    last_name;

        public function 
    __construct(first_namelast_name) {
            
    $this->first_name first_name;
            
    $this->last_name last_name;
        }
    }

    class 
    User extends Person {
        private 
    user_id;
        private 
    username;
        private 
    password;

        public function 
    __construct(first_namelast_nameusernamepassword) {
            
    parent::__construct(first_namelast_name);
            
    $this->username username;
            
    $this->password password;
        }

    You get the drift...

    The area where it gets tricky is properly synchronizing the person_id on select and update. You'll figure it out though.

  4. #3
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Only one problem, I can't remember anything about public / protected / private lol. As soon as I got out of that class I wiped it from memory lol. OOP was a fun class though, 3 hrs long on Wednesdays, 15 min break in middle to go to Subway across the road

    Forgot to mention this is PHP / mySQL.

    Existing table that exists is like so:
    Code:
    TABLE user (
        user_id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        field1 mediumtext NULL,
        field2 mediumtext NULL,
        field3 mediumtext NULL,
    ) TYPE-InnoDB;
    So then I'd make a new table like so:
    Code:
    CREATE TABLE list (
        list_id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL REFERENCES user (user_id),
        field1 mediumtext NULL,
        field2 mediumtext NULL,
        field3 mediumtext NULL,
    ) TYPE-InnoDB;
    I'll muck w/ it tomorrow.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Protected scope is similar to private, except that it can be accessed by child classes (those which extend the parent)

  6. #5
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Basically what I want to do is to create a dynamic set of profile fields. For example, say someone creates 5 profile fields so their users can list their various PC components (which is what most of my vB mods revolve around anyway). The current system works just fine until they get a 2nd PC they'd like to add. Currently the only solution is to add 5 new fields and pop them into a category named 2nd PC, but what about those users who have 10 computers? 50 profie fields could be set up, but it would waste DB space for the multiple users who don't own more than one computer.


    What I'm trying to do is to extend the profilefield table to add a unique ID field for enhanced functionality. This way, the DB size is minimized, but still efficient. If I could somehow force any standard vB calls for profile fields to where userID=whatever, and listID=1, I could easily accomplish this. Unfortunately there's no easy way to do that as far as I can tell, so I'm looking into creating a similar system to plug into the forum.

    Just had the idea of extending the built in vB query functions. Now it's been years since I've messed with OOP, but I THINK where needed I could include a call to a php function that extends the vB query functions. The new functions could review the query to see if it's calling for profile fields, and if so set the listID to 1 unless otherwise supplied.


    Am I thinking along the right lines?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  7. #6
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    You don't need OO DB, you need 3rd normal form:

    Code:
    CREATE TABLE user (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(255) NOT NULL,
        password VARCHAR(255) NOT NULL
    ) TYPE-InnoDB;
    
    CREATE TABLE computer_brand (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL
    ) TYPE-InnoDB;
    
    CREATE TABLE computer (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL REFERENCES user(id),
        computer_brand_id INT NOT NULL REFERENCES computer_brand(id),
        password VARCHAR(255) NOT NULL
    ) TYPE-InnoDB;
    
    CREATE TABLE component_type (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL
    ) TYPE-InnoDB;
    
    CREATE TABLE component (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        computer_id INT NOT NULL REFERENCES computer(id),
        component_type_id INT NOT NULL REFERENCES computer(component_type_id),
        name VARCHAR(255) NULL,
        details TEXT NULL
    ) TYPE-InnoDB;
    This allows users to have many computers and many parts per computer, and also organizes computer brands and component types into their own tables for referential integrity.

  8. #7
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Since I'm dealing with vBulletin, the user table already exists. I hadn't thought of computer brand actually. That gives me an idea. I could make a computer model table, and limit it to certain spec options for the end user. If the user selects custom, then of course all options would be available for the specs.


    Code:
    CREATE TABLE computer_brand (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL
    ) TYPE-InnoDB;
    
    CREATE TABLE computer_model (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        computer_brand_id INT NOT NULL REFERENCES computer_brand(id)
    ) TYPE-InnoDB;
    
    CREATE TABLE computer_spec_list (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL REFERENCES user(id),
        computer_model_id INT NOT NULL REFERENCES computer_model(id)
    ) TYPE-InnoDB;
    
    CREATE TABLE component_type (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL
    ) TYPE-InnoDB;
    So here's a rundown with some fake data:

    Table computer_brand
    --id= 6
    --name = custom

    Table computer_model
    --id = 12
    --name = custom
    --computer_brand_id = 6


    Table computer_spec_list
    --id = 1
    --user_id = 456
    --computer_model_id = 12

    Table component_type
    --id = 17
    --name = Motherboard

    So far so good. Here's where there could be 2 different organizations though. Most components won't need to be linked to a computer model, as they're pick and choose. However, the reverse may be wanted in the case of OEM systems.

    The hurdle at this part is that some users of this code may not care about duplicate information, whereas some might, and will only want users to select data from a selection dropdown. Of course the latter scenario will probably want the option of allowing the end user to enter in their own data, and then have it moderated and approved by the admin. Actually most of these tables should be moderated, but that's as simple as a switch for each row (approved / not approved).


    gotta figure out this part later, gotta go home

    Code:
    CREATE TABLE component (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        computer_model_id INT NOT NULL REFERENCES computer_model(id),
        component_type_id INT NOT NULL REFERENCES computer(component_type_id),
        name VARCHAR(255) NULL,
        details TEXT NULL
    ) TYPE-InnoDB;

    Table component
    --id = 934
    --computer_model_id =
    --name =
    --details =


    Password on the computer table? methinks you've copied and pasted, tsk tsk!

    BTW, why InnoDB? I googled it and found this article:
    The standard MyISAM table type is ideal for website use, where there are many reads in comparison to writes, and no transactions.
    This sounds like the better fit. Opinion?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  9. #8
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    InnoDB allows FOREIGN KEY references. Makes it better for referential integrity, but poorer in performance. vBulletin doesn't have any foreign keys as is, so you might as well continue that and use MyISAM for speed.

  10. #9
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Code:
    CREATE TABLE component_list (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        user_id INT NOT NULL REFERENCES user(id)
    ) TYPE-MyISAM;
    
    CREATE TABLE component (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        details TEXT NULL,
    	component_list_id INT NOT NULL REFERENCES component_list(id),
        component_model_id INT NOT NULL REFERENCES component_model(id)
    ) TYPE-MyISAM;
    
    CREATE TABLE component_model (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        component_brand_id INT NOT NULL REFERENCES component_brand(id),
        component_type_id INT NOT NULL REFERENCES component_type(id),
        component_URL_id INT NULL REFERENCES component_URL(id),
    	) TYPE-MyISAM;
    
    CREATE TABLE component_brand (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL
    ) TYPE-MyISAM;
    
    CREATE TABLE component_type (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL
    ) TYPE-MyISAM;
    
    CREATE TABLE component_URL (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL
    ) TYPE-MyISAM;
    Here's what I have so far. It's geared towards custom built systems. I figure for OEM systems I can create component lists with a user ID of 0, which would indicate templates. In the PHP if a user ID = 0, then all users can access the list, copy / save it as their own, and then modify.

    Next step is figuring out how to moderate user entered data. Either I can add a boolean entry on some of the tables for approval. Query can find the non-approved ones, and if approved, they will then show up. OR, all user entered data can be placed into duplicate test tables, and when approved they can be removed from those tables and moved into the live tables.

    Thoughts?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  11. #10
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Lookin good! That's a 3rd normal form database! :-D

    As for user-entered data, you should have a status_id column on each of your tables referencing another table (status). Possible statuses could be "new/pending", "approved/active", "rejected/inactive", etc.

    You'll also have to program a way to merge data together, e.g. if someone creates a new item that already exists. It should do an UPDATE to change all referring items of with one reference to the reference it's merging into, then update the status of the reffered item to inactive.


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 08:55 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com