Design issue: Better to have one table with similar records of differe

Downside to having more tables but same number of records?

Page 1 of 1

2 Replies - 797 Views - Last Post: 23 July 2010 - 12:49 PM Rate Topic: -----

#1 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Design issue: Better to have one table with similar records of differe

Posted 23 July 2010 - 07:57 AM

Hello all,

I am developing a PHP site and have run into an issue with the database design. The site has a number of requests that can be sent form one member of the site to another. Right now there are two and I am about to add a third kind. The type of requests are invitations to become associates within the site, requests for referrals from other members, and requests to join a group. These all have very similar requirements database wise with only a few differences between them. There very well could be other request type requirements in the future as the site gets further along in development.

My choices are : Keep creating separate tables for each type of request with fields that are specific to them. Or I could make a generic request table with a request_type_id FK that would specify the type of request. This table would have fields that apply to all types of requests so it would result in some records having null fields. For example a friend request would not need a group_id but a group invitation would, so for requests of type friend request the group_id field would be null.

My questions are, should I do what I can do make this one generic table or make multiple specific tables for each type of request? Either way I would have the same number of records. What would be the advantages/disadvantages of doing this either way? Any advice would be really appreciated. Thanks much!


Jstall

Is This A Good Question/Topic? 0
  • +

Replies To: Design issue: Better to have one table with similar records of differe

#2 girasquid  Icon User is offline

  • Barbarbar
  • member icon

Reputation: 108
  • View blog
  • Posts: 1,825
  • Joined: 03-October 06

Re: Design issue: Better to have one table with similar records of differe

Posted 23 July 2010 - 10:51 AM

Make multiple, specific tables.

If you don't, as things change and grow (because they will), you'll end up with a monstrous table that has 40 different columns, with only 10 getting used for any one request type.

If you have multiple specific tables, you know exactly where the data you want is located, without having to do any bizarre querying. Compare:
SELECT id, message, sender FROM requests WHERE type='invitation' AND group NOT NULL;


vs:
SELECT id, message, sender, group FROM group_invites;



While you can definitely get by with either, from a maintenance point of view you'll have a much easier time if you make specific tables for each thing (at least, in my opinion).
Was This Post Helpful? 2
  • +
  • -

#3 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: Design issue: Better to have one table with similar records of differe

Posted 23 July 2010 - 12:49 PM

Thanks for the reply, you make some good points and I think I will stick with keeping them as separate tables :D



Jstall
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1