1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Mysql Database Structure

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

  1. Stalli0n

    Stalli0n Junior Member

    Joined:
    Nov 17, 2010
    Messages:
    115
    Likes Received:
    83
    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:
    115
    Likes Received:
    83
    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
    Home Page:
    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
    Home Page:
    oh yaa. sry. unique not distinct.