问题描述:

I am looking for better solution for my below requirement.

I have to store user's favorite thing like -

  1. Movies

    1. Name
    2. Type // Like sci-fi, thriller, comedy, romantic
    3. URL [ If any ]
  2. Music

    1. Name
    2. Type
    3. URL [ If any ]
  3. Cuisine

    1. Name
    2. Type
    3. URL [ If any ]
  4. Sports

    1. Name
    2. Type
    3. URL [ If any ]

etc.,

This favorite list can grow more & For each Favorite.

These above columns will be for each favorite items. My Current solutions is creating Table for each favorite thing, like [Favorite movies, Favorite Sports, etc.]. But as these favorite items may increase, and i don't want to create another table for that again from MySQL.

So, i am looking for solution like , Creating table with name just, "favorite", with column :-

  1. Name.
  2. Page Link.
  3. Type.

Those name can be "movies", "books" etc., And inside each "movie" or "books", There will be all data. And Those data will be linked to different user.

So, I need help in Desgining Schema of Table with Relation, by creating less table.

Programming Language, I am using :-

  1. MySQL for Database/
  2. PHP for Server side.

Thanks

网友答案:

You can create a master table tbl_favoritemaster to store list of favorite items (e.g. Movies, Books etc...) and with child table tbl_favoritedetail to store user's favorite thing (e.g. User wise movie details, User wise books details etc...). Using this structure you are not required to create more tables for all favorite item list.

1. tbl_favoritemaster

Column List: favoriteId (PK), favoriteName
Data: 

| favoriteId | favoriteName |
|      1     | Movie        |
|      2     | Book         |

2. tbl_favoritedetail

Column List: Id (PK), favoriteId (Reference to tbl_favoritesmaster), Name, Type, URL
Data: 

| Id | favoriteId | Name | Type | URL  |
|  1 |    1       | M1   |  T1  | URL1 |
|  1 |    2       | B1   |  T2  | URL2 |
网友答案:

The way that I would approach this.

______________________________________________________________
| UniqueID | FavouriteID | Data | Name    | Page Link | Type |
| Int(11)  | Int(11)     | Blob | varchar | Varchar   | Int? |
______________________________________________________________

Second table

___________________________
| UniqueID | FavouritName |
| INT(11)  | VarChar      |
___________________________

Create a foreign key between Table 2 UniqueID and Table 1 FavouritID

when accessing via PHP I use this set of functions myself

function RDataLink($field, $table, $current=""){
accessDB();
    $QTable=mysql_query("SHOW CREATE TABLE $table");
    $Table=mysql_result($QTable,0,1);
exitDB();
// CONSTRAINT `jobSeeker_ibfk_1` FOREIGN KEY (`location`) REFERENCES `24_location` (`UID`)
$x=preg_match_all('/FOREIGN[^(]+.[^(]+[^)]+./',$Table,$matches);
foreach ($matches as $array){
    foreach ($array as $value){
        if(preg_match("/(?<=FOREIGN\sKEY.{3})$field/",$value)){
            if (preg_match("/(?<=REFERENCES[^`].)[^`]+/",$value,$ForeignTable)){
                accessDB();
                    $Qrows=mysql_query("SELECT * FROM ".$ForeignTable[0]);
                exitDB();
                $options="<select name=\"$field\">";
                while ($rows=mysql_fetch_array($Qrows,MYSQL_ASSOC)){
                    $options.="<option value=\"".$rows['UID']."\"".($current==$rows['UID'] ? "selected" : "") .">".$rows['name']."</option>";
                }
                $options.="</select>";
            }
        }
    }
}
if(!$x){
    return false;
}
    else {
    return $options;
}
}

function RQuery($field,$table,$current){
accessDB();
    $QTable=mysql_query("SHOW CREATE TABLE $table");
    $Table=mysql_result($QTable,0,1);
exitDB();
$x=preg_match_all('/FOREIGN[^(]+.[^(]+[^)]+./',$Table,$matches);
foreach ($matches as $array){
    foreach ($array as $value){
        if(preg_match("/(?<=FOREIGN\sKEY.{3})$field/",$value)){
            if (preg_match("/(?<=REFERENCES[^`].)[^`]+/",$value,$ForeignTable)){
                accessDB();
                    $Qrow=mysql_query("SELECT name FROM ".$ForeignTable[0]." WHERE UID='$current'");
                    return mysql_result($Qrow,0);
                exitDB();
            }
        }
    }
}

}

May take some looking at to apply to your situation. but I hope it is helpful

网友答案:

Since Movies, Music... may have different properties, having a base table for favorable entities may serve:

相关阅读:
Top