Why have a 1:1 Relationship - Database Design? -
i'm looking @ existing database schema , i'm bit confused regarding 1:1 realtionship in example below:
event ----- id int (pk) title varchar description varchar organizerid int (fk) eventschedule ------------ id int (pk) eventid int (fk) start datetime end datetime repeatrule varchar (ical format repeating events) venue ----- id int (pk) eventid (fk) name varchar address1 varchar address2 varchar city varchar region varchar postcode varchar latitude float longitude float
an event ever takes place @ 1 location there 1:1 relationship between event , venue. event has 1:1 relationship eventschedule - ical repeat rule captures repeating events.
would there reason or instance separating tables this? wrong in having single table follows:
event ----- id int (pk) title varchar description varchar organizerid int (fk) start datetime end datetime repeatrule varchar (ical format repeating events) venue varchar address1 varchar address2 varchar city varchar region varchar postcode varchar latitude float longitude float
some advise on pros/cons of each design appreciated in above context make schema flexible enough future consideration though can't possibly think of reason above relations ever change in real world scenario i.e. 1:1 1:n etc.
one reason separate avoid use of nulls in venue , schedule table if not known @ time event record set up.
another reason separate have one-to-one relationship now, may planning future when don't have one-to-one relationship. have seen these types of things events in multiple locations , have multiple schedules.
and since venues reused different events, have designed venue table , eventvenue table avoid repeating address , gps data.
another reason separating may have how data consumed. if don't need information in of queries makes sense spread them out other tables.
yet reason separating many people prefer model based on logically falls together.
another reason separating has tables less wide tend faster query (particularly if don't need query other information) , fields makes table wider effective row size sql server record. if max size of each field met cause unable add records table though sql server let create it.
Comments
Post a Comment