...

View Full Version : MySql normilization help



x3kt
12-18-2007, 05:55 PM
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


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?


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-

GJay
12-18-2007, 06:46 PM
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.

x3kt
12-18-2007, 07:42 PM
Thanks for the response GJay, i've made the changes you suggested.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum