Jump to content


Photo

Need some help with table normalization


  • Please log in to reply
1 reply to this topic

#1 Malkavbug

Malkavbug
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 08 April 2006 - 09:47 PM

Okay, going to try and draw this out as best I can. Here is the issue, I am trying to make a message board for financial stuff, including the stock market.

The board will be threaded conversation on several boards which need to be displayed by market, symbol, or industry, so individual threads need the ability to be flagged as pretaining to one, or several stocks, which in turn will flag them as industry or market threads based on the stock, or they need the option to be flagged as just a general industry or a general market.

I am trying to do this right and normalize my tables to avoid tons of redundant data, so here is where I am at.

I hope I am describing this okay.

In general I will need a
Users Table (U), with just basic account information for the user, which will have a one to many relationship with T and P

For the General Stock stuff I will need a
Market Table (M) , which will have a one to many relationship with I and S
Industry Table (I) , which will have a one to many relationship with M and S
Stock_Symbol Table (S) , which will have a one to one relationship with M and I

Then for the message boards I will need
A Threads Table (T) , with a one to many relationship with (P)
A Posts Table, with a many to one relationship with (T)


For the Joins I will need (this is where I am getting a headache)
A join table to bring MIST together, so table will have the information on the Market, Industry, Symbol, and Thread it applies to.

A join table which will bring PUT together, so a thread can generate and show the message posts



If anyone can point out flaws in this basic sketch or give recommendation on how to go from here I would appreciate it. I want to try and do this right the first time, but I have no experience dealing with table normalization.



#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 April 2006 - 06:18 PM

Sounds good so far -- as long as all of the data you're storing is atomic, and you've pulled out all the possible relationships, then you're on your way.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users