9 Replies - 755 Views - Last Post: 17 May 2011 - 08:40 AM

#1 xtremer360  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 123
  • Joined: 03-March 11

Db Structure

Posted 09 May 2011 - 12:24 PM

Okay so say I have a table for scheduled wrestling events that looks like this:

ID : (auto- increment, primary key)
eventNameID: (ID of the event name from the eventNames table)
label: (label of the event will either be the next incrementing number from the previous occurance of event or the year in which its happening)
bookingDate: (date of which the event is taking place)
numMatches: (number of matches set for the event)
numSegments: (number of segments set for the event) (a segment might be like a backstage interview for example)
arenaID: (ID of the area from the arenas table)
eventStatusID: (ID of the event status from the eventStatuses table)


The next step after creating the event is setting up the matches and segments. My form is setup for an area for the show introduction, and for each of the matches for the event it has the following:
match type: singles, tag team, etc.
title: any of the title belts that could be on the line (can have more than one attached)
stipulation: any of the stipulations that could added to the match (can have more than one attached)
competitors: a list of characters that are competting for the match (will have more than one attached)
match writer: ID of the person responsible for writing the match
match title: the title for the match NOT the title belt but the title
preview: a preview for hyping up the match

And for each of the segments is the following:

segment Writer: ID of the user responsible for writing the segment
segment After: a dropdown that represents the Introduction, and each match which dynamically field because of it telling how many matches there are in the event

I'm trying to come up wth as many ideas as the BEST way to store all this information.

Is This A Good Question/Topic? 0
  • +

Replies To: Db Structure

#2 the1corrupted  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 165
  • Joined: 31-March 09

Re: Db Structure

Posted 10 May 2011 - 04:27 AM

Well there's several ways you could approach this. For the 'match type' field, you could store it as a string (as a TEXT or BLOB but CHAR would work if you know the length). As for the title, stipulation, and competitors you might consider making a second table specifically for that because you can have an undefined number of them attached to it.

It would be structured: Reference ID (the ID number you want all of them to associate by, index not primary and not unique), title (possibly numeric since you have a set number of titles that you could translate. E.g. 1 through 9), stipulation may have to be stored as a string (again, TEXT or BLOB).

But the other half of this is how you handle it when you parse out the data. Sometimes database flags are necessary to catch the exceptions. If there's only a set amount of differences, consider creating switches and storing your data numerically (for those really long strings).

Hope this helps :)
Was This Post Helpful? 1
  • +
  • -

#3 xtremer360  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 123
  • Joined: 03-March 11

Re: Db Structure

Posted 10 May 2011 - 09:01 AM

View Postthe1corrupted, on 10 May 2011 - 05:27 AM, said:

Well there's several ways you could approach this. For the 'match type' field, you could store it as a string (as a TEXT or BLOB but CHAR would work if you know the length). As for the title, stipulation, and competitors you might consider making a second table specifically for that because you can have an undefined number of them attached to it.

It would be structured: Reference ID (the ID number you want all of them to associate by, index not primary and not unique), title (possibly numeric since you have a set number of titles that you could translate. E.g. 1 through 9), stipulation may have to be stored as a string (again, TEXT or BLOB).

But the other half of this is how you handle it when you parse out the data. Sometimes database flags are necessary to catch the exceptions. If there's only a set amount of differences, consider creating switches and storing your data numerically (for those really long strings).

Hope this helps :)



So your saying something like this:

Table: EventSegments (used for insert EACH of the Event Segments for an event)
Fields:
ID: (auto incremented primary key)
eventID: (ID of the event)
MatchWriterID: (ID of the user responsible for writing segment)
type: (Match or Segment)
SortOrder: (order in the event the segment should appear)
SegmentTitle: (Title for the individual segement that gets written, ie. "Backstage Interview with whoever")
matchTypeID; (ID of the match type if the eventSegment is a match. Will only be one value.)
preview: (Preview to hype the match)
submitted: (YES or NO for if the event Segment has been submitted)

Table: TitleMatches (used for inserting for EACH of the titles if there is one)
Fields:
ID: (auto incremented primary key)
eventSegment: (ID of of the Event Segment)
titleID: (ID of the title being competed for)

Table: StipulationMatches (used for inserting for EACH of the stipulations if there is one)
Fields:
ID: (auto incremented primary key)
eventSegment: (ID of of the Event Segment)
stipulationID: (ID of the stipulations being added to the match)

Table: CompetitorMatches (used for inserting for EACH of the competitors for the match)
Fields:
ID: (auto incremented primary key)
eventSegment: (ID of of the Event Segment)
competitorID: (ID of the wrestler competing for the match)

Or do you think i should make a separate table for the matchtypeID? How does the rest look. And I thank you for your first response. Just so you are aware everything that has and ID at the end of it are ALL integers.

This post has been edited by xtremer360: 10 May 2011 - 09:06 AM

Was This Post Helpful? 0
  • +
  • -

#4 the1corrupted  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 165
  • Joined: 31-March 09

Re: Db Structure

Posted 10 May 2011 - 07:00 PM

Here's something to help put it in perspective as far as associative tables go:

Table: Users
ID (Primary, indexed, unique, unsigned int)
User
Password
Email

Table: Inventory
ID (Primary, indexed, unique, unsigned int)
Item
Desc
Cost

Table: Usr_Inv
Item_Id (indexed, unsigned int)
Usr_Id (indexed, unsigned int)
Quantity (unsigned mediumint)

To call all the items a user has (as well as finding out how many), essentially do a join select statement: "Select `inventory`.`item`, `usr_inv`.`quantity` FROM `usr_inv`, `inventory` WHERE `inventory`.`id`=`usr_inv`.`inv_id` AND `usr_inv`.`inv_id`=<insert user ID>"

This way, all data is associated with just two columns in the table usr_inv (shorthand for User Inventory) And let's say the item quantity drops to zero:
"DELETE FROM `usr_inv` WHERE `quantity`<=0 AND `usr_id=<insert user id>"

When I want to add an item to a users inventory:
"INSERT INTO `usr_inv` (`item_id`, `usr_id`, `quantity`) VALUES (1, 2, 3)"
When handling the insert, I usually have a redundancy to check if a row with the user id and item id already exists. Update the old or insert the new.

This post has been edited by the1corrupted: 10 May 2011 - 07:00 PM

Was This Post Helpful? 0
  • +
  • -

#5 xtremer360  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 123
  • Joined: 03-March 11

Re: Db Structure

Posted 10 May 2011 - 07:13 PM

View Postthe1corrupted, on 10 May 2011 - 08:00 PM, said:

Here's something to help put it in perspective as far as associative tables go:

Table: Users
ID (Primary, indexed, unique, unsigned int)
User
Password
Email

Table: Inventory
ID (Primary, indexed, unique, unsigned int)
Item
Desc
Cost

Table: Usr_Inv
Item_Id (indexed, unsigned int)
Usr_Id (indexed, unsigned int)
Quantity (unsigned mediumint)

To call all the items a user has (as well as finding out how many), essentially do a join select statement: "Select `inventory`.`item`, `usr_inv`.`quantity` FROM `usr_inv`, `inventory` WHERE `inventory`.`id`=`usr_inv`.`inv_id` AND `usr_inv`.`inv_id`=<insert user ID>"

This way, all data is associated with just two columns in the table usr_inv (shorthand for User Inventory) And let's say the item quantity drops to zero:
"DELETE FROM `usr_inv` WHERE `quantity`<=0 AND `usr_id=<insert user id>"

When I want to add an item to a users inventory:
"INSERT INTO `usr_inv` (`item_id`, `usr_id`, `quantity`) VALUES (1, 2, 3)"
When handling the insert, I usually have a redundancy to check if a row with the user id and item id already exists. Update the old or insert the new.


And this helps me how?
Was This Post Helpful? 0
  • +
  • -

#6 the1corrupted  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 165
  • Joined: 31-March 09

Re: Db Structure

Posted 11 May 2011 - 08:05 AM

Quote

Table: EventSegments (used for insert EACH of the Event Segments for an event)
Fields:
ID: (auto incremented primary key)
eventID: (ID of the event)
MatchWriterID: (ID of the user responsible for writing segment)
type: (Match or Segment)
SortOrder: (order in the event the segment should appear)
SegmentTitle: (Title for the individual segement that gets written, ie. "Backstage Interview with whoever")
matchTypeID; (ID of the match type if the eventSegment is a match. Will only be one value.)
preview: (Preview to hype the match)
submitted: (YES or NO for if the event Segment has been submitted)

You shouldn't need a sort order... It's the fun of having the "ORDER BY" in the SQL query.

Quote

Table: TitleMatches (used for inserting for EACH of the titles if there is one)
Fields:
ID: (auto incremented primary key)
eventSegment: (ID of of the Event Segment)
titleID: (ID of the title being competed for)

You don't need the auto-incrimented primary id.

But rather than these tables:

Quote

Table: StipulationMatches (used for inserting for EACH of the stipulations if there is one)
Fields:
ID: (auto incremented primary key)
eventSegment: (ID of of the Event Segment)
stipulationID: (ID of the stipulations being added to the match)

Table: CompetitorMatches (used for inserting for EACH of the competitors for the match)
Fields:
ID: (auto incremented primary key)
eventSegment: (ID of of the Event Segment)
competitorID: (ID of the wrestler competing for the match)


Table: Matches
MatchTypeID
competitorID
eventSegment (This will associate this match to the segment)

table: Stipulations
eventSegment
stipulationID

table: Titles
eventSegment
titleID

You really don't need more than those two columns listed under each. But everything, I assume, you would want associated to the event segment id.

This post has been edited by the1corrupted: 11 May 2011 - 08:08 AM

Was This Post Helpful? 1
  • +
  • -

#7 xtremer360  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 123
  • Joined: 03-March 11

Re: Db Structure

Posted 11 May 2011 - 08:35 AM

Well I will need a sortOrder because otherwise it's just going to list the matches then the segments and won't have them in order according to where they appear in the event.

And I'm thinking what you put for the table Matches might need separated because there's going to be more than one row for the competitors in a match and there will only be one value in the matchtypeID.
Was This Post Helpful? 0
  • +
  • -

#8 the1corrupted  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 165
  • Joined: 31-March 09

Re: Db Structure

Posted 12 May 2011 - 11:55 AM

That MatchTypeID was a typo when I posted it.

(And leave the MatchTypeID on the Segment table. So if you have a match ID, you search for competitors and/or stipulations)

This post has been edited by the1corrupted: 12 May 2011 - 11:56 AM

Was This Post Helpful? 1
  • +
  • -

#9 xtremer360  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 123
  • Joined: 03-March 11

Re: Db Structure

Posted 12 May 2011 - 12:24 PM

You are aware that matchTypeID and match ID would be TWO different things right?
Was This Post Helpful? 0
  • +
  • -

#10 the1corrupted  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 165
  • Joined: 31-March 09

Re: Db Structure

Posted 17 May 2011 - 08:40 AM

View Postxtremer360, on 12 May 2011 - 01:24 PM, said:

You are aware that matchTypeID and match ID would be TWO different things right?

No, but you should be started on the right path now.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1