Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: Design Questions

    Hello,

    I am creating a project and am unsure how to design the database.

    I will have entities in an entity table
    I will have subscribers in a subscriber table.

    Each subscriber can be subscribed to multiple entities.

    My question is should each subscription be its own record so a subscriber might be in there 30 times connected to 30 different entities or should each subscriber be entered in only once and all subscriptions saved in a serialized array field?

    I know the best way is normalization and no arrays or json or anything like that but what if there are 2 million subscriptions in 18 months? There will be 2 million rows in one table vs maybe knocking off half if storing serialized arrays of subscriptions per subscriber.

    I want to go with no arrays but what are performance issues of a table with millions of records and growing? Is it better that when it hits a million that my system automatically makes a second table and just joins the tables when looking for a subscriber?

    On a similar note, same problem but with messages being sent out to hundreds of people. A message gets created by an entity and saved in a message table. It is then sent to hundreds of subscribers. Do I save a serialized array of the hundreds of ids of subscribers the message was sent to in a field of the message record or do I add hundreds of records to a Sent to table with the message id in each one? The latter would be best for data manipulation but that table could get HUGE quick. Could archive tables by month just fine but even in one month there could be millions of messages sent.

    So anyway, any type of theory, thoughts, opinions would be appreciated. Thanks!

    Josh

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by morcth View Post
    My question is should each subscription be its own record so a subscriber might be in there 30 times connected to 30 different entities or should each subscriber be entered in only once and all subscriptions saved in a serialized array field?
    definitely the former

    storage space is no longer an issue -- it might have been back in the day when 1 megabyte was a hunnert dollars, but today you can get several terabytes for that

    performance depends on indexing, index your tables properly and you'll be fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    2
    Thanks r937,

    Yeah no worried about storage space, just performance but did read up more int he last day and see really that millions of records is fine if indexes are optimized correctly. I will get it all working and then read up on that. Thanks for the help.

    Josh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •