Database Design – How to Model Weak Entity in ER Diagram

database-designerd

I was going through the weak entity concepts and was trying to model the relation Hotel has many rooms. I have come up with the partial solution presented below but i'm having a problem to uniquely identify the tuples in the room entity related to the specific hotel.

My Assumption

Hotel(hotel_number, name) is a strong entity.
Rooms(room_number, bed) is a weak entity whose existence depends on the existence of the hotel.

1. Hotel entity's table

hotel_id	name
125	n1
132	n2
173	n3
103	n4
193	n5

2. Room entity's table

room_number	beds
101	3
101	2
102	1
103	6

There are two hotels with hotel_id 125 and 132. Hotel with hotel_id 125 has room numbered 101 with 3 beds and another hotel has room_number 101 with 2 beds. So how can i model the given relation in such condition as there's no pk for the weak entity i.e. room ??

Best Answer

Room should have PK of (hotel_id,room_number).

Weak entities have compound keys with the strong entity key/foreign key as the leading column(s).

There are people who disagree with this approach, but they are wrong.

How can i find the room belonging to specific hotel which has the different number of beds limiting the attributes in the domain of my problem

Something like:

select *
from rooms
where hotel_id  = 1001
  and beds = 3

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 UNIQUE (RoomType, Zone, Name), 
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.

How to model entities in an entity-relationship diagram about a car service scenario

The General Advice:

When you are starting off learning how to model databases, one of the most important rules of thumb is: Every tangible thing that matters to your system is probably an entity type.

This is a really good place to start with any logical database design. If you spend some time up front thinking about what kind of things matter to your system, then you're going to come up with a solid foundation on which to build your system. The things your organization cares about will change much less frequently than the business processes and rules your organization uses to deal with those things. That is why a solid data model is so important.

Another important rule of thumb is: Normalize your data model by default and only denormalize when you have a (really) good reason to. This is especially true for a transactional system. Reporting systems and data warehouses are a different story.

The Specific Answers:

Cardinality: If you think about it, it is easily the case that a car could have never been serviced (by your shop). Therefore a minimum cardinality of zero is very plausible. On the other hand, by the time the vehicle matters to your system it may well be because it has had its first service - so a minimum cardinality of one is also plausible. You need to think about what the business rule is for your organization and model accordingly. I would think, for example, that a car dealership would have lots of cars in its system that haven't been serviced by the dealership yet, whereas a muffler shop wouldn't care about cars it hasn't serviced.

Service Items: You asked:

Also, a service involves parts, labor, and consumable.

How would you model this? As a separate entity? Or in the service entity or part of the relation (intersection entity) between car and service ?

Let's consider an intersection entity between car and service... You could potentially use such an intersection to store details about the service, like how much labour, which parts, and consumables were used.

However, using an intersection implies a many-to-many between cars and services, but you've already stated that each service is for (exactly?) one car. Using an intersection entity to track service item details would mean your model isn't properly normalized.

Consider this model as an alternative:

ERD alternative 1

In this model each service is for one vehicle, but each service can have many instances of labour, parts and consumables. This model follows the first rule of thumb I mentioned and makes an entity type out of each tangible thing the system cares about. This might be a good first stab at a logical model.

One of the issues with the above model is that it doesn't handle one aspect of how your system is likely to want to use the data, at least not very well. One of the most important reasons for tracking all of this data in your system at all is so that you can print off an itemized service invoice. That means that a service line item is itself a thing which is important to your system. If you take that into consideration, you might end up with something more like this:

ERD alternative 2

Notice in this second alternative SERVICE_LINE_ITEM is recognized as an entity type. It is an intersection between SERVICE and the generic line item type: SKU. A SKU is a supertype entity that could be a part, a consumable or some kind of labour. You don't need to have a logical supertype for service line item types, but a lot of systems would be modeled this way because it makes the transactional detail much simpler.

This second model introduces abstract entities over and above the concrete entities of the first model. Introduction of abstractions like this is one of the things that tends to happen as you move from an initial logical model, based mostly on tangible things to a physical model.

As you gain experience with data modeling, you'll get good instincts for moving past the conceptual/logical model stage directly to a well structured physical model.