Help modelling an ER Diagram

Question

I am trying to model the following 2 sentences in an ER Diagram:

Each company has a set of tanks. It is also known for each tank how filled up it is for each time of day.

It is not known if there are specific times of day for which there are logs. It can be once a day, twice, each hour, maybe some days you don't have any info, etc.

This is my current attempt:

(I forgot to put in the Tanks/Dates association an attribute called "current load")

Although for some reason I don't like it (I guess I don't like the Dates entity!). How do you guys generally model this kind of situations?

I think I could instead have a TankLoads entity, with an attribute "Load" and another attribute on the association called "Date".

Thanks

PS: Sorry for the crappy DIA Diagram :(

Answer 1

Hmm, I would do that slightly different. Put all dates / times in a separate table (I called it Dates for now, but you can also call it times or timestamps or so), and many-to-many them with Tanks. (I used the table FillLevels.)

The level of which a tank is filled is stored in the FillLevels table.

Companies:
- [...]

Tanks:
- company_id: int
- [...]

FillLevels:
- tank_id: int
- time_id: int
- [...]

Dates:
- [...]

I'm sorry for not having a shiny diagram for you, but I home this makes a bit of sense.

Alternatively, you could create a DateTime field in the FillLevels table, instead of using the Dates table, depending on how you intend to query the database.

Answer 2

Data modeling and database design are not exactly the same thing, even though people often use ER diagrams to summarize database schemas.

There's nothing "unreal" about a date, or a timestamp composed of a date and time. The fact that they are abstractions is no big deal. A bank account is just as abstract.

Whether you are going to have a table of dates or timestamps is another story. In practice, I've found it very helpful to have a table that catalogues every date, or every workshift, in reporting databases. With appropriate attributes, it makes it trivial to produce reports that are weekly, or monthly, or quarterly, or yearly, even when the enterprise has its own quirky fiscal calendar. Some of the attributes might be redundant with functions that are built into SQL, but even then it's a relatively inconsequential design decision.

Answer 3

If you are looking for an E-R Model for a transactional database which is optimal for inserts and queries, then the following should satisfy your requirement. Please comment if you have already thought of this and did not go for it for some reason.

Tanks is a child table of company and fill_levels is a child table of tanks. By creating a unique constraint (or unique index), you make sure there is only one record per tank per fill time in the fill_levels table.

create table company(
  company_id number primary key,
  company_desc varchar2(200) not null
);

create table tanks (
     tank_id number primary key,
     company_id number,
     tank_desc varchar(200) not null,
     constraint fk_tanks_company foreign key(company_id) references company(company_id)
);

create table fill_levels(
     fill_levels_oid number,
     fill_date date not null,
     tank_id number not null,
     other_attr varchar2(200),
     constraint fk_fill_levels_tanks foreign key(tank_id) references tanks(tank_id),
     constraint unq_tank_fill_levels unique(tank_id, fill_date)
);

Answer 4