-3

I have these concepts for a database application: Session, Subsession and File

  • A session can have multiple files
  • A session can have multiple subsessions
  • A subsession can have multiple files
  • A session and subsession are completely different concepts, they don't have much in common so they cannot be merged into one table.

I am trying to decide between 2 alternative schemas or maybe a better schema that I couldn't think of.

The first schema I designed with 3 tables is like this:


Sessions Table

  • SessionID: Primary key
  • SessionField1
  • SessionField2
  • ...

Subsessions Table

  • SubsessionID : Primary key
  • SessionID
  • SubsessionField1
  • SubsessionField2
  • ...

Foreign key constraint: SessionID => Sessions.SessionID

Files Table

  • FileID: Primary key
  • SessionID: Nullable
  • SubsessionID: Nullable
  • FileName: VARCHAR
  • CreationDate: DateTime

Foreign key constraint: SessionID => Sessions.SessionID

Foreign key constraint: SubsessionID => Subsessions.SubsessionID


This table structure has the following disadvantage:

If a file belongs to a subsession, then should I still fill in the SessionID field in Files table? It would be redundant since SessionID information is already kept in the Subsession. But if I don't fill it in, then I need to make sure either SessionID or SubsessionID is filled in, but not both of them.

So I came up with an alternative table structure, this time with 4 tables:


Sessions Table

  • SessionID: Primary key
  • SessionField1
  • SessionField2
  • ...

Subsessions Table

  • SubsessionID : Primary key
  • SessionID
  • SubsessionField1
  • SubsessionField2
  • ...

Foreign key constraint: SessionID => Sessions.SessionID

SessionFiles Table

  • FileID: Primary key
  • SessionID: Nullable
  • FileName: VARCHAR
  • CreationDate: DateTime Foreign key constraint: SessionID => Sessions.SessionID

SubsessionFiles Table

  • FileID: Primary key
  • SubsessionID: Nullable
  • FileName: VARCHAR
  • CreationDate: DateTime

Foreign key constraint: SessionID => Sessions.SessionID


And the disadvantage of this schema is that if I decide to change the schema of Files, I need to change 2 tables, so I need to keep them in sync since they basically represent the same object, just with different owners (session or subsession).

Which schema is a better practice in your opinion? Or is there a better schema design for these types of situations?

  • Can a File be associated with only a single Session or a single SubSession, or is there a many-to-many relation possible? – Bart van Ingen Schenau Sep 5 '19 at 10:16
  • A file can be associated to only one Session or one Subsession. – Canol Gökel Sep 5 '19 at 12:12
3

If files can be associated either with sessions or subsessions, you should consider using separate association tables for that.

Session table

  • Id
  • ...

SubSession table

  • Id
  • Session -> references Sessions.Id
  • ...

Files table

  • Id
  • FileName
  • ...

SessionFiles table

  • Session -> references Session.Id
  • File -> references Files.Id

SubSessionFiles table

  • SubSession -> references SubSessions.Id
  • File -> references Files.Id

If you want to enforce the unique ownership of files, you'll want a custom constraint for that.

0

Assumed sessions and subsessions have more properties in common than just the property of being a container for files, I would avoid to have two different tables Session and Subsession. Just give Session a nullable foreign key field ParentSessionID. Where this field is NULL, the session is a "normal" one, where it is filled, the session is a subsession.

Now you Files only needs a SessionID field.

  • Sorry I should have made clear in the question that Session and Subsession are completely different concepts with their own fields. I will edit my question to reflect that. Still thank you for your answer, that is indeed a good solution when session and subsession represent the same thing. – Canol Gökel Sep 5 '19 at 8:43
  • @CanolGökel: even if session and subsession require different fields, I guess they also have some commonalites? Then make 3 tables: MainSession, Subsession, and Session, where Session bundles the commonalities of MainSession and Subsession, and records have a 1:1 relationship from MainSession to Session as well as from Subsession to Session. – Doc Brown Sep 5 '19 at 9:00
  • Sessions and subsessions unfortunately have almost entirely different fields. – Canol Gökel Sep 5 '19 at 9:06
  • @CanolGökel: then the common "Session" table will be small, but nevertheless solving you problem. – Doc Brown Sep 5 '19 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.