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 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Dec 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post MySql normilization help

    Hello,

    I'm currently developing an online TB Sci-Fi strategy game; I've completed the design document
    and am now working on designing the database.

    I haven't had any experience with database design in the past, and have been browsing various MySql tutorials.

    I've now made an attempt at designing one of the tables I'll be requiring, it will be storing buildings that players
    have built within their colonies.

    please take a look at my current table and indicate to me any faults that I have made.


    Initial

    Code:
    structures
    user_id
    struct_id
    struct_lvl
    struct_hp
    struct_sp
    struct_xp
    struct_def
    struct_quantity
    current_staff
    max_staff
    current_usage
    max_storage
    
    stuct_alias
    struct_id
    struct_alias
    Current
    correctly normalized?

    Code:
    struct_attributes
    user_id		MEDIUMINT UNSIGNED NOT NULL	//user id
    struct_id	SMALLINT UNSIGNED NOT NULL	//structure id
    struct_lvl	SMALLINT UNSIGNED NOT NULL	// " " level
    struct_hp	MEDIUMINT UNSIGNED NOT NULL	// " " hitpoints
    struct_sp	MEDIUMINT UNSIGNED NOT NULL	// " " sheildpoints
    struct_xp	BIGINT UNSIGNED NOT NULL	// " " experience
    struct_def	MEDIUMINT UNSIGNED NOT NULL	// " " defense
    struct_qauntity MEDIUMINT UNSIGNED NOT NULL	// " " quantity
    
    struct_properties
    struct_id	SMALLINT UNSIGNED NOT NULL      //id of structure
    struct_alias	VARCHAR[40] NOT NULL		//structures name
    struct_staff	ENUM('T','F') NOT NULL		//*
    struct_storage 	ENUM('T','F') NOT NULL		//#
    
    *True - search staff table for stats, False - don't seach staff table
    #True - search storage table for stats, False - don't seach storage
    
    struct_staff
    user_id		MEDIUMINT UNSIGNED NOT NULL	//user id
    struct_id       SMALLINT UNSIGNED NOT NULL	//structure id
    current_staff	SMALLINT UNSIGNED NOT NULL	// " " employeed
    max_staff	SMALLINT UNSIGNED NOT NULL	// " " max staff
    
    struct_storage
    user_id		MEDIUMINT UNSIGNED NOT NULL	//user id
    struct_id	SMALLINT UNSIGNED NOT NULL	//structure id
    current_usage	SMALLINT UNSIGNED NOT NULL	// " " occupied space
    max_storage	SMALLINT UNSIGNED NOT NULL	// " " maximum space
    -x3kt-
    Last edited by x3kt; 12-18-2007 at 07:48 PM.

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    I wouldn't have the _staff and _storage tables, I'd put current_X into the struct_attributes table, and the max_X into the struct_properties table instead of the booleans, having NULL for the max and current values when they don't apply.

    Other than that, it looks fine assuming that the various points are specific to an 'instance' of a building, and that users can only have one of each type- if they have more than one then there isn't any way to tell them apart, in that case I'd give the _attributes table an autoincrement id as the primary key.
    My thoughts on some things: http://codemeetsmusic.com
    And my scrapbook of cool things: http://gjones.tumblr.com

  • #3
    New to the CF scene
    Join Date
    Dec 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the response GJay, i've made the changes you suggested.


  •  

    Posting Permissions

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