Mysql Database Structure

Discussion in 'Other Languages' started by Stalli0n, Jun 24, 2011.

  1. Stalli0n

    Stalli0n Junior Member

    Joined:
    Nov 17, 2010
    Messages:
    116
    Likes Received:
    84
    Location:
    Europe
    Hey, i need some advice for my database.

    I have a few different types of tasks i need to manage.

    These are the tables:

    Task
    ID, StartDate, Status, ....

    CreateTask
    TaskID, x, x, x, x

    PostTask
    TaskID, x, x

    Now PostTask and CreateTask have different fields so i can't put it into one table.

    But i need to connect them somehow because when the worker.php gets a new task from the database he should get the task with the soonest StartDate. So a CreateTask or a PostTask.

    So i use the Task table to select all tasks but its giving me some troubles.

    Because when i want to insert a new task i have to do this:
    lock tables task write, createtask write
    select count(*) from task (the new ID)
    insert into task
    insert into CreateTask
    unlock tables

    Thats a lot more work than just a single insert and the lock tables is giving me some troubles.

    So is there maybe better solution without the Task table?
     
  2. other_henry

    other_henry Junior Member

    Joined:
    Jun 1, 2011
    Messages:
    107
    Likes Received:
    19
    Occupation:
    Freelance coder, server guy
    Location:
    US
    Why are you locking tables and not doing simple inserts?

    If you need to relate tables give the records IDs & join on them
     
  3. Stalli0n

    Stalli0n Junior Member

    Joined:
    Nov 17, 2010
    Messages:
    116
    Likes Received:
    84
    Location:
    Europe
    Well the tables task and createtask are connected with the taskid.

    And many threads are adding new tasks simultaneously so if i don't lock the tables it could happen that 2 createtasks have the same taskid
     
  4. other_henry

    other_henry Junior Member

    Joined:
    Jun 1, 2011
    Messages:
    107
    Likes Received:
    19
    Occupation:
    Freelance coder, server guy
    Location:
    US
    If you are using auto increment columns then mysql won't create duplicate ids.
     
  5. adbox

    adbox Power Member

    Joined:
    May 1, 2009
    Messages:
    658
    Likes Received:
    107
    What i say may be completely off track, but:

    I'd setup my tables like this;

    Task Table
    id INT(11) primary key auto-increment
    ...other fields

    Create Table
    id INT(11) primary key auto-increment
    task_id (this would hold the id of the task)
    ...other fields

    Post Table
    id INT(11) primary key auto-increment
    task_id (this would hold the id of the task)
    ...other fields

    I'm not sure if you need to connect Create & Post tables. But they will connect at task_id.

    I'm not sure what you are wanting to prevent duplicates on. But you can place a DISTINCT(EDIT: I should of said UNIQUE not DISTINCT 'other_henry explains why below') criteria on a field to prevent duplicate entries. In Mysql there is an INSERT INTO ON DUPLICATE UPDATE query that whenever a duplicate entry is detected, instead of canceling the query it will run an update on the entry that has the same field.
     
  6. other_henry

    other_henry Junior Member

    Joined:
    Jun 1, 2011
    Messages:
    107
    Likes Received:
    19
    Occupation:
    Freelance coder, server guy
    Location:
    US
    Distinct is a criteria for a query. Use Unique to define a column that can't have duplicates.

    Setting 'primary key' in a column's definition forces it to be unique so it will not accept duplicate records
     
  7. adbox

    adbox Power Member

    Joined:
    May 1, 2009
    Messages:
    658
    Likes Received:
    107
    oh yaa. sry. unique not distinct.