1 Replies - 352 Views - Last Post: 16 July 2013 - 04:37 PM Rate Topic: -----

#1 Chainy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-July 13

Normalising and building ERD

Posted 16 July 2013 - 02:30 PM

Hello, I'm a first year IT student and I failed my Data Modelling exam and as a way of preparing for my resit, I decided to take on a project that could help me get more familiar with ERDs and normalizing a database. There's no text to work from, just a set of ideas I had that I would like to include in my project, and then I started working from there. The set of features I started with is:

  • Users
  • Clans
  • Events
  • Timers
  • Posts
  • Ranking
  • Badges


and from that I attempted to create, in words, a set of requirements of what everything should do:

"The goal of this project is to provide clans with a platform to manage and maintain their users. Each user has a specific rank within a clan, and a user can only be part of one clan. Clan members have the ability to host and attend events, add attendees and upload a screenshot showing who was at the event. Each event is put on a clan calender when approved and a timer is used to indicate when the next event is happening (which is derived from the EventDate and EventTime). If enabled, users who subscribe to the event will get a notification sometime before the event starts. Certain staff members in a clan are able to create announcements for said clan, and users can also subscribe to this news feed (so they'll get a notification).

Owners can customize their clan ranks to change the threshold needed to proceed to the next rank. Users go up in ranks by gaining badges in the clan. A badge has a name, a description, a requirement, a weight and can be stackable. Owners can also change the weight of a badge."

That's what I came up with in words, and then I attempted to make an ERD out of it, I managed to get this so far: http://i.imgur.com/9BJQYz2.png

I excluded the attributes to make the image smaller, but the attributes I have so far for them are:

User

  • Login
  • Password
  • Email
  • DisplayName
  • Clan
  • ClanRank
  • Timezone
  • Membership
  • Avatar
  • DO_NOT_DISPLAY_AVATAR
  • Signature
  • DO_NOT_DISPLAY_SIGNATURE
  • UserAlbum (points to the root of the user's pictures directory on the server) -- actually, maybe this isn't needed considering this will be derived from login... /login/album
  • UserBadges (points to all the Badge objects a given user has)
  • SUB_TO_EVENTS
  • SUB_TO_ANNOUNCEMENTS
  • Active


Clan

  • ClanName
  • Owner (references a User)
  • Timezone
  • Specilisation (PvP, PvE, ...)
  • Motto
  • Description


Event

  • EventName
  • EventHost (references User)
  • EventAttendees (references User objects)
  • EventStartDateTime (references a Timer object)
  • EventAlbum
  • Recurring


Timer

Not quite sure how to make these two work together, and if it's even a good idea to.



Post

  • Author (references User)
  • Title
  • Body
  • DatePublished
  • TimePublished


I have no idea how to work Ranking in there at all.

I'd like someone to review what I have so far, give me pointers and what to improve on to have a really good normalized model. I don't know if the text I wrote makes it clear enough, if something's confusing or whatnot I'll try to explain what I'm trying to do better.

Thanks in advance, trying to get better at this so all tips and pointers are more than welcome!

Is This A Good Question/Topic? 0
  • +

Replies To: Normalising and building ERD

#2 Chainy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-July 13

Re: Normalising and building ERD

Posted 16 July 2013 - 04:37 PM

I couldn't find an edit button, so I figure a reply will have to do. Using http://www.dreaminco...-normalization/ I think I've been able to change my design for the better already. Here's what I have now:

+--------------------+
| CLAN               |
+--------------------+
| CLAN_ID: PK        |
| CLAN_NAME          |
| CLAN_MOTTO         |
| CLAN_DESCRIPTION   |
| SPECIALIZATION: FK |
| TIMEZONE: FK       |
+--------------------+



+--------------------+
| USER               |
+--------------------+
| USER_ID: PK        |
| USER_LOGIN         |
| USER_PASSWORD      |
| USER_EMAIL         |
| USER_DISPLAYNAME   |
| CLAN_ID: FK        |
| RANK_NAME: FK      |
| TIMEZONE: FK       |
| MEMBERSHIP         |
| AVATAR             |
| DND_AVATAR         |
| SIGNATURE          |
| DND_SIGNATURE      |
| SUBCRIBE_EVENTS    |
| SUBSCRIBE_POSTS    |
| BADGE_ID: FK       |
+--------------------+



+-------------------+
| TIMEZONE          |
+-------------------+
| TIMEZONE_NAME: PK |
| TIMEZONE_UTC      |
+-------------------+



+----------------------------+
| SPECIALISATION             |
+----------------------------+
| SPECIALISATION_NAME: PK    |
| SPECIALISATION_DESCRIPTION |
+----------------------------+



+-------------------+
| EVENT             |
+-------------------+
| EVENT_ID: PK      |
| USER_ID: FK       |
| CLAN_ID: FK       |
| EVENT_NAME        |
| EVENT_DESCRIPTION |
| EVENT_DATE        |
| EVENT_TIME        |
+-------------------+



+--------------------+
| PARTICIPANT (WEAK) |
+--------------------+
| USER_ID: FK        |
| EVENT_ID: FK       |
+--------------------+



+------------------+
| RANK             |
+------------------+
| RANK_NAME: PK    |
| CLAN_ID: FK      |
| RANK_THRESHOLD   |
| RANK_DESCRIPTION |
+------------------+



+-------------------+
| BADGE             |
+-------------------+
| BADGE_ID: PK      |
| BADGE_NAME        |
| BADGE_DESCRIPTION |
| BADGE_REQ         |
| BADGE_XP          |
+-------------------+



+--------------------+
| ALBUM              |
+--------------------+
| ALBUM_ID: PK       |
| USER_ID: FK,NULL   |
| CLAN_ID: FK,NULL   |
| EVENT_ID: FK,NULL  |
| ALBUM_NAME         |
| ALBUM_DESCRIPTION  |
+--------------------+



+---------------------+
| PICTURE             |
+---------------------+
| PICTURE_ID: PK      |
| ALBUM_ID: FK,NULL   |
| PICTURE_NAME        |
| PICTURE_DESCRIPTION |
+---------------------+



+-------------+
| POST        |
+-------------+
| POST_ID: PK |
| USER_ID: FK |
| POST_TITLE  |
| POST_BODY   |
| POST_DATE   |
| POST_TIME   |
| TAG_ID: FK  |
+-------------+



+-------------+
| TAGS        |
+-------------+
| TAG_ID: PK  |
| TAG_NAME    |
| POST_ID: FK |
+-------------+



I haven't drawn out the relationships here but I was thinking the following:

  • A clan has many users
  • Clans have a specialisation
  • Clans have a timezone
  • Users can be a member of a clan
  • Users have a rank within a clan
  • Users have a timezone
  • Users can have badges
  • Users can host different events
  • Events have participants
  • Events have albums
  • Users have albums
  • Clans have albums
  • Albums have pictures
  • Users with a certain rank can make posts for their clan
  • Posts have tags


That's what I could think of so far. How does this look in comparison to what I started out with, is this one better or worse than the previous, can I get feedback on the better one and how to improve it?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1