Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    what is the best way to set up this database

    I want to set up a system for my company.

    The company has many departments. There are workers in those departments. Weekly each department will upload statistics of a set number of jobs that will be the same every week.

    To set it up by possible tables

    The database is the company, never changing.

    Departments will have workers added sometimes but never removed because this is a log system

    Workers will never change, only added to. There are 5 different types of workers so that you can compare types of workers in all departments on specific projects

    projects are all the same in every department. Weekly output per department will be uploaded displaying how those workers did in that department

    Output per worker per project. Average output per person ~700 (can be decimals)

    %output per worker per project. Each project =100%, values can be decimals.

    How this information will be accessed is through a series of website dropdown menus as follows.

    Department > project > list of all uploads, each containing the weekly output of the workers. This would display the workers

    Type of workers > projects, this would list the top outputs ever uploaded.

    And that's all of it. I am new to this so please excuse my ignorance. I do not know how I should set these tables up, how to make tables associate with other tables etc... Any help or guidance would be extremely appreciated

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    You will pardon me for suggesting that this sounds like homework for some class.

    It's hard for me to imagine that a human boss would give this to a human programmer who did not yet have the knowledge needed to set up such a simple database.

    Then, too, your use of "can be decimals" sounds like something an instructor would put into a homework assignment.

    Assuming that it is homework, then see Rule 1.5 here:
    http://www.codingforums.com/rules.htm

    If this is not homework, then can you convince us it is not? Why would you be assigned a project like this when you have no database experience at all, it would seem?

    On the other hand, if it is homework, then at least come up with *SOME* design and then allow us to critique it. *THAT* we can do. Don't make us do you homework for you, against the rules.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm sorry, this actually relates to a game in which the departments are guilds, projects are bosses. The data is the damage the group has done etc....

    I thought it would be easier to not mention anything related to a game. It's a logging system for different guilds meters so they can compare against eachother

  • #4
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't think you can edit posts so sorry for double posting. I'm optimistically trying to do this personal project and I'm pretty ignorant. I'm hoping to learn

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    LOL! See how much better it is to tell the truth! No reason not to mention it is for gaming; I'll bet 20% of the stuff we see in these forums (maybe more) is game-related. Granted, generally in the JS section, not MySQL, but...

    Code:
    CREATE TABLE users (
        userid INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50),
        password VARCHAR(50),
        isActiveUser BIT(1) DEFAULT TRUE,
        userlevel INT,
        ...other fields ...
    ) ENGINE INNODB;
    
    CREATE TABLE group (
        groupid INT AUTO_INCREMENT PRIMARY KEY,
        groupname VARCHAR(50),
        ... other fields ...
    ) ENGINE INNODB;
    
    CREATE TABLE group_members (
        groupid INT,
        userid INT,
        CONSTRAINT FOREIGN KEY (groupid) REFERENCES groups(groupid),
        CONSTRAINT FOREIGN KEY (userid) REFERENCES users(userid)
    ) ENGINE INNODB;
    
    CREATE TABLE project (
        projectid INT AUTO_INCREMENT PRIMARY KEY,
        projectname VARCHAR(50),
       ... other fields ...
    ) ENGINE INNODB;
    
    CREATE TABLE project_work (
        projectid INT,
        groupid INT, 
        userid INT,
        workAmount DECIMAL(20,4),
        workPercent DECIMAL(20,4),
        CONSTRAINT FOREIGN KEY (projectid) REFERENCES projects(projectid),
        CONSTRAINT FOREIGN KEY (groupid) REFERENCES groups(groupid),
        CONSTRAINT FOREIGN KEY (userid) REFERENCES users(userid)
    ) ENGINE INNODB;
    The above assumes that a given user can be a member of more than one group (would makes sense if "group" identifies a group of players for a game, and you have more than one game at a time). And of course PROJECT throughout could be replaced by GAME, more than likely.

    If a user can only be a member of a single group, forever and ever, no changing groups, then we can simplify a couple of things. But I doubt that is realistic.

    Notice that I added an [b]isActive[b] flag, so that you can change it to FALSE when somebody drops out. That way his/her logon is disabled by the statistics for her/him are still there.

    userlevel is for your 5 different "types of workers".
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry for the slow update, trying to educate myself while applying what you gave me. I'll have an update soon.

  • #7
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    After trying to set up the database with what you gave me I was given this error.

    MySQL said:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.. other fields ...
    ) ENGINE INNODB' at line 7

    I'm going to try to troubleshoot this and I will post back if I'm able to get last this error. Mind you I was using your code on phpmyadmin through an sql query.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    LOL!

    I was just telling *YOU*, not MySQL, that you could put OTHER FIELDS needed for your application into that table at that point.

    I guess I didn't realize you were that much a newbie to SQL that you would think that was valid SQL.

    Next time I do something like that I'll put it in red italics and then explain it's for the programmer to change or add to.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •