Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    1:1 Relationship - Database Design

    I'm looking at an existing database schema for a new application and I'm a bit confused regarding the 1:1 relationship in the example below:

    Code:
          
    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 for 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 only ever takes place at one location so there is a 1:1 relationship between the Event and Venue. Similarly an Event has a 1:1 relationship with the EventSchedule - the ical repeat rule captures repeating events.

    Would there be any reason or instance for separating tables like this? What would be wrong in having a single table as follows:

    Code:
    Event
    -----
    id int (PK)
    Title varchar
    Description varchar
    OrganizerId int (FK)
    Start datetime
    End   datetime
    RepeatRule varchar (ical format for repeating events) 
    Venue  varchar
    Address1 varchar
    Address2 varchar
    City varchar
    Region varchar
    Postcode varchar
    latitude float
    longitude float
    Basically the application presents a form for organizers to add events which would include providing Venue/Address details. It would be a single form for both create/edit functionality that has all the fields from the above design. User would be able to search for event by name, location and date.

    Some advise on pros/cons of each design would be appreciated specifically in the above context to make the schema flexible enough for any future consideration though I can't possibly think of any reason where the above relations would ever change in a real world scenario i.e. 1:1 to 1:N etc.

  2. #2
    Join Date
    May 2016
    Posts
    83
    • Hi ozzii


    considering the information, here is the conceptual data model

    Click image for larger version. 

Name:	2016-09-22_20-47-00.jpg 
Views:	4 
Size:	84.8 KB 
ID:	17133

    Thus you don't need to enter each time times the same information, but just select in cascading lists


    Some questions:

    • Only one organizer organize an event
    • Could an event be located in several venues?

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by informer View Post
    • Hi ozzii


    considering the information, here is the conceptual data model

    Click image for larger version. 

Name:	2016-09-22_20-47-00.jpg 
Views:	4 
Size:	84.8 KB 
ID:	17133

    Thus you don't need to enter each time times the same information, but just select in cascading lists


    Some questions:

    • Only one organizer organize an event
    • Could an event be located in several venues?

    There is no venue/address lookup table for organizers to select from. The venue/address is created manually by doing a google address lookup when creating the event.

  4. #4
    Join Date
    May 2016
    Posts
    83
    HI Ozzii,

    here is the conceptual data model:

    Click image for larger version. 

Name:	event_cmd.jpg 
Views:	2 
Size:	40.8 KB 
ID:	17135

    and logical data model
    Click image for larger version. 

Name:	event_lmd.jpg 
Views:	2 
Size:	34.7 KB 
ID:	17136

    In this case
    • only one person can organize an event
    • If a same event occurs several times, you need to create as many times as this event occurs.


    I suggest you to change cardinalities between event and venue which should be (1,n) - (1,n), generating a new entity with attribut like dateEventStart , dateEventEnd (see below)

    Click image for larger version. 

Name:	event_cmd2.jpg 
Views:	1 
Size:	42.8 KB 
ID:	17137

    Click image for larger version. 

Name:	event_lmd2.jpg 
Views:	2 
Size:	44.4 KB 
ID:	17138

  5. #5
    Join Date
    Mar 2007
    Posts
    212
    Hi,

    Have a look at the following site for creating an event. https://www.eventbrite.co.uk/create

    Basically I'm trying to replicate the same database schema. How would you implement a logical data model that represent the same as the above website?

    In short it has the following functionality:

    A single organizer can create multiple events.

    Each event can repeat itself ( e.g. Swimming 101 can occur every Friday for 5 weeks starting from 23 Sept 2016)

    An event takes place at a Venue.

    A Venue has a location (Address and GPS co-ordinates - lat/long)

    An organizer will manually enter the Venue/Address details. They can also edit these details.

    Click image for larger version. 

Name:	Event.png 
Views:	4 
Size:	23.2 KB 
ID:	17139

  6. #6
    Join Date
    May 2016
    Posts
    83
    Hi,

    Considering your needs, a list of values available only for
    • Country
    • Frequency
    • Day


    I propose you this data model

    CMD
    LDMClick image for larger version. 

Name:	event_cmd.jpg 
Views:	1 
Size:	70.7 KB 
ID:	17140

    Click image for larger version. 

Name:	event_lmd.jpg 
Views:	3 
Size:	60.4 KB 
ID:	17141

    With the next one prerequisite, the organizer has a profile in your information system !

    The last question: What is a venue because organizer enters address, county, postcode and so on !

Tags for this Thread

Posting Permissions

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