CODEWITHSUNDEEP
mysqldatabasedoctrine-orm
MrGlass
MrGlass

Reputation: 9262

Many to many relationships with large amount of different tables

I am having trouble developing a piece of my database schema. Currently, my app has a table of users, and a another table of events. I can easily set up a many to many relationship (using a third table) to hold information about which users are attending which events.

My problem is that events is just one feature of my app. The goal is to have a large number of different programs a user can take part in, and each will need its own table. Yet I still need to be able to call up a list of everything the user is signed up for.

Right now, I am thinking about just making one way relationships from each event table back to the user. I would then need to create a custom function (in my websites ORM) that queries each table independently and assembles a full list. I feel like this would be slow, so I am also entertaining the idea of creating a separate table that just list all the programs that users sign up for, and storing in there the info needed for my app to function. This would repeat info in my database, and in general doesn't sound as "clean", but probably would be faster.

Any suggestions as to the best way to handle relationships like this?

P.S. If it matters, I'm using Doctrine2 & Symfony2 to power my site.

Upvotes: 2

Views: 514

Answers (3)

manniar3
manniar3

Reputation: 51

In one of my web applications, I have used a this kind of construct for storing comments for any table that has integer as primary key:

CREATE TABLE Comments (
    Table VARCHAR(24) NOT NULL,
    RowID BIGINT NOT NULL,
    Comments VARCHAR(2000) NOT NULL,
    PRIMARY KEY (TABLE, RowID, COMMENTS)
);

In my case (DB2, less than 10 million rows in Comments table) it performs well. So, applying it to your case:

CREATE TABLE Registration (
    Table VARCHAR(24) NOT NULL,
    RowID BIGINT NOT NULL,
    User <datatype> NOT NULL,
    Signup TIMESTAMP NOT NULL,
    PRIMARY KEY (TABLE, RowID, User)
);

So, the 'Table' column identifies the table containing the program (say, 'Events' table). 'RowID' is the primary key in that table (e.g. PK of an entry in 'Events' table). To perform well, this requres the primary key being of same datatype in all target tables.

NoSQL solutions are cool, but pattern above works in plain old relational database.

Upvotes: 5

one.beat.consumer
one.beat.consumer

Reputation: 9504

What is unique about these event types that requires them to have their own table?

If the objects are so inherently different, make the object as simple as possible with only those things common to all Events:... 

public Event
{
    public Guid Id;
    public string Title;
    public DateTime Date;
    public string Type;
    public string TypeSpecificData; // serialized JSON/XML
}

// Not derived from Event, but built from it.
public SpecialEventType
{
    public Guid Id;
    // ... and the other common props from Event

    // some kind of special prop parsed from the Event's serialized data
    public string SpecialField; 
}

The "type specific data" could then be used to store details about events that are not in common (that would normally require columns or new tables)... do it something like serialized XML or JSON

Map the table MTM to your Users table, and query by the basic event properties and its type.

Your code is then responsible for parsing the data using its Type property and some predefined XML schema you associate with it.

Very simple, keeps your database nice and clean, and fast, minimizes round trips. The tradeoff here is that you don't have the ability to query the DB for the specifics of a certain Event type... but for large scaling applications, with mature ORM layers, the performance tradeoff is worth it alone...

For example, now you query your data once for Events of a particular Type, build your pseudo-derived types from it, and then "query" them using LINQ.

Upvotes: 2

MEURSAULT
MEURSAULT

Reputation: 8819

Unless you have a ridiculous amount of types of events, querying events a user is signed up for from a few tables should not be much slower than querying the same thing from one long table of all the events.

I would take this approach, each table or collection has a user_id field which maps back to the Users table. You dont need to really create a separate function in the ORM. If each of the event types inherit from an event class, then you can just find all events by user_id.

Upvotes: 1

Related Questions