database-designentity-relationshipconceptual-model

Conceptual modelling scenario

I am very new to Database Design and I have been practising with as many questions as I can. I happened to come across the below question (No, this is not my assignment!) and from what I could understand, I have created the attached conceptual model. There is some information missing in the model because from the requirements, it wasn't clear to me where should they be added. I have highlighted the lines that I have a doubt in.

You have been assigned the task to design a database schema that captures the information needed for a facility reviewing service for tourists, for facilities such as accommodations, eateries and planned trips. With regards to the accommodations, the service keeps information about the name, the address, the kind of accommodation. The accommodation type could be hotel, hostel or bed & breakfast. We need also the name and address. There is also an optional set of facilities (which should be listed, separately) such as the kind of rooms (single, double or for more persons), the presence of TV in rooms, bathroom, etc. More than one facility could be applicable to the same place. There is also the cost per night which could be specified either per person (in hostels) or per room (in hotels, bed and breakfasts; therefore, depending on the accommodation type).
For the places to eat we have the name and the address (unique and always available). They could be restaurants, bars, pubs, taverns and self-services. It should be specified the kind of cuisine, the average cost per meal (divided into 4 possible cost levels), the daily time table (composed by the open and close times) and optionally the stop days during the week (one or more days). With regards to the trip, we want to specify the list of the names of the attractions (and corresponding address, if present). For each trip attraction, we have the interval of dates in which the tourists visit them. Each customer can leave a review in free text for each place (accommodation or place to eat but not for tourist trips), specifying his/her nickname (which is unique for all the customers), the date of visit (or alternatively a calendar interval specified by two dates) and a integer score (from 0 to 5). Each customer could visit and leave a review more than once for the same place. Draw the ER diagram for the conceptual design of the database.

This is what I came up with: Conceptual Model

The image has the model that I could think of. My doubts are:

1) Is my approach correct to use so many generalizations? Is there any other way?

2) In the description above, the first sentence that is in italics and bold says that there are certain 'optional set of facilities'. Should these attributes be added to Hotel, Hostel and B&B entity or to the generalised Accommodation entity?

3) In the second sentence that is highlighted, should the cost be added to Hotel,Hostel and B&B like I have done? Otherwise, how should I proceed in modelling this?

4) In the third highlighted sentence, should the specified attributes be listed under each type of eatery or should they be added to the generalised entity Eatery?

Thanks much for the help in advance!

Solution

  • Is my approach correct to use so many generalizations? Is there any other way?

    There certainly are other ways. There's nothing wrong with generalization, though its rare to see so much of it in one model. That doesn't mean its wrong, right and wrong in this case depends on how well your model represents the business requirements.

    In the description above, the first sentence that is in italics and bold says that there are certain 'optional set of facilities'. Should these attributes be added to Hotel, Hostel and B&B entity or to the generalised Accommodation entity?

    It sounds like a general requirement, I would associate it with Accommodation.

    Note that there are two usages of "Facility" in the given scenario - for places that can be visited/reviewed, and for features of rooms. I would recommend renaming one of the terms to avoid confusion.

    In the second sentence that is highlighted, should the cost be added to Hotel,Hostel and B&B like I have done? Otherwise, how should I proceed in modelling this?

    You could do it the way you've done, or add a cost attribute (and per person/room indicator) to Accommodation.

    You should try to avoid mixing domains in an attribute. By this I mean that all possible values should be of the same type and logically interchangeable. A common situation is mixing values of different currencies in one attribute, or the value column in EAV tables. Such designs tend to increase complexity.

    Adding a cost attribute to Accommodation has a similar smell, combining cost per room and cost per person in a single attribute. Nevertheless, I would consider it if it could eliminate a set of subtypes - if we have no subtype-specific attributes, relationships or constraints, type can be indicated by an attribute, reducing the need to explicitly model each subtype.

    In the third highlighted sentence, should the specified attributes be listed under each type of eatery or should they be added to the generalised entity Eatery?

    I see no reason to list it separately for each eatery. Attributes, relationships and constraints that are common to all subtypes should be associated with the supertype.

    For comparison, here's my model. I did it before looking at yours, so it takes a very different approach.

    Facility reviews ERD

    Some notes:

    I hope this helps, let me know if I should clarify anything.