Dynamic tables and classes

[Yesideez]

I'm writing a large web application similar to a classifieds section in a newspaper. A friend bought one and asked me to take a look at it and the coding was terrible so I thought I'd write one from scratch.

 

Just wondering the best way to approach this part...

 

People sell all sorts of items and the data required for each item can vary quite considerably. For example, an advert selling a car won't need to contain information about CPU speed or quantity.

 

Instead of making what could potentially be hundreds of tables what would be the best way to approach this writing a class (I'm using PHP5) to handle something like this? My tables look similar in structure to this...

 

class db_users {
  const TABLENAME='users';
  public $id=null;
  public $username='';
  public $password='';
  public function __construct($id) {
    if ($id!=null) {$this->id=$id;}
  }
  public function save() {
    if ($this->id==null) {
      mysql_query("INSERT INTO ".self::TABLENAME." SET
        `username`='".dbsafe(substr($this->username,0,20))."',
        `password`='".dbsafe(substr($this->password,0,40))."'");
    } else {
      mysql_query("UPDATE ".self::TABLENAME." SET
        `username`='".dbsafe(substr($this->username,0,20))."',
        `password`='".dbsafe(substr($this->password,0,40))."'
        WHERE `id`=".$this->id);
    }
  }
  public function retrieve() {
    if ($r=mysql_query("SELECT * FROM ".self::TABLENAME." WHERE `id`=".$this->id)) {
      $this->username=$r['username'];
      $this->password=$r['password'];
    }
  }
}

There's much more to it than I've posted there but it gives an idea of how I'm handling the data - perfect for tables that don't change but if I wanted to include a dynamic table I've no idea how I can incorporate it into code.

 

Any suggestions?

[KevinM1]

What do you mean by 'dynamic table'?

 

Database normalization is the process of creating new tables in order to eliminate anomalies in storing, editing, and retrieving data.  So, an aversion to having many tables stems from not wanting to do the tedious work rather than a good design decision.  I can't say I blame you, as DB work is incredibly boring, but you'll wind up tanking your entire site if you don't bite the bullet and attempt to do it right the first time.

 

I'm wary about mapping objects to tables, especially in the way you're looking at it now.  You're not saving yourself any work by mirroring the DB in objects.  Let the DB handle the heavy lifting - that's what it's there for, and it's much more efficient to keep the data manipulation there.  A DB object should, instead, be:

 

1. Generalized.  It should be able to handle any database and any tables within that database.

2. A convenient way to access the database.  It should be used as a simple proxy API to keep the client code as simple and readable as possible.

 

So, instead of having a class db_users, and db_cars, and db_computers, and others, you should have one class that can handle queries and retrieve results.

 

Also, unless you have reason for the client code to have direct access to your object's data members, you should declare them as private, and create accessor functions to manipulate them should the need arise.  Otherwise, you kill your object's encapsulation.

[Yesideez]

That's what I'm trying to do - do it right first time but I've only ever played with tables that don't need to change and have no idea how to safely handle tables that change.

 

If anyone is able to link me to a tutorial or script that has one function (inside a class!) handling all reads and writes that'd be great!

[KevinM1]

That's what I'm trying to do - do it right first time but I've only ever played with tables that don't need to change and have no idea how to safely handle tables that change.

 

Properly designed tables don't change, at least not in terms of structure.  You shouldn't be adding/removing entire table columns on the fly.  I'm not sure if that's what you mean by 'dynamic tables' or not, but it sounds like you're overthinking it.  Read up on database normalization (if you Google it, one of the top results is a great article on the MySQL site).  I think that will solve your problem.

[dbo]

I understand what you're trying to accomplish Yesideez. You want to allow users to define their own "types" on the fly such that you can account for flexibility without the need to make coding changes.

 

You can do this by designing your database table structure in such a way that you have a table or tables that represent the item type (table name), table(s) that represent the columns and datatypes, and then ultimately a way to link that to individual data elements. What you end up with is a data set that is stored like this:

 

Row1-Field1

Row1-Field2

Row1-Field3

 

etc

 

Instead of

1, Field, Field, Field

 

So then you have to do some SQL trickery to roll that up into a single record such that you can query it as usual. This approach is fine and does provide end users a lot of flexibility. The limitation comes in however when you get into creating "types" that have relationships (very difficult to enforce referential integrity at this point) and the scalability of such a beast is unknown. There are a lot of subqueries and work that has to go on to create that record so performance will take a hit as the amount of data you're storing grows.

[Yesideez]

The one thing I've recently changed is the way I've been handling the table name. Ran into a situation when I wrote an installer allowing the EU to select a table prefix...

class db_users {
  public $tablename='users';
  public $id=null;
  public $username='';
  public $password='';
  public function __construct($tableprefix,$id) {
    $this->tablename=$tableprefix.$this->tablename;
    if ($id!=null) {$this->id=$id;}
  }

 

I'm not sure how (or if) this is going to affect the way I can get around but one thing's for sure, I sure am stumped!