Entities (Database Design)

Anonymous
2020-01-25T17:54:20+00:00

Hello,

So I've seen people use the word 'entity' to describe one-to-many relationship types. Defined as 'where an entity can be related to one or more entities, but each of the other entities can be related to only one from the first entity type.' . Wow, is it just me or is this wordy? lol

Is entity the table/what the table represents? Is the entity (referring to start of the definition...'where an entity') always referring to the most granular level of something? Is that how the design works? from most granular to more broad? Is that one way to think of it?

What does each of the OTHER entities can be related to only ONE from the first entity?

Thanks for any clarifications.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments
Answer accepted by question author
Anonymous
2020-01-26T20:54:49+00:00

That's pretty much the case.  The key word when defining a relationship type is 'each':

1.  If each row in TableA can reference one or more rows in TableB and each row in TableB can reference only one row in TableA then the relationship type is one-to-many, with TableA being the referenced table, and TableB being the referencing Table.  The primary key in the referenced table is referenced by a foreign key in the referencing table.

2  If each row in TableA can reference only one row in TableB and eachrow in TableB can reference only one row in TableA then the relationship type is one-to-one , with TableA being the referenced table, and TableB being the referencing Table.  The primary key in the referenced table is referenced by a foreign key in the referencing table.  The foreign key is also the primary key of TableB.

  1. If each row in TableA can reference one or more rows in TableB and each row in TableB can reference one or more rows in TableA then the relationship type is many-to-many.  This is modelled by a third table which resolves the many-to-many relationship type into two one-to-many relationship types, i.e. it has two foreign keys referencing the primary key of the TableA and TableB respectively.

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates, amongst other things, the various relationship types and how they are modelled.

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

26 additional answers

Sort by: Most helpful
  1. Anonymous
    2020-01-25T22:24:52+00:00

    "An entity can simultaneously be of more than one entity type.  This is the case in a type hierarchy.  For example an employee can be of both Employees and Programmers entity types, where Programmers is a sub-type of type Employees."

    I think a good way to understand this is to think of the Employee and Programmer Entity Types as Role-based Entities. In other words, the same physical entity (person) can take on different roles in different situations or transactions.

    That's a great way of putting it.

    So then, is one-to-many relationship simply the idea that one thing (ie one person..one customer, one employee) can take on many roles?

    Was this answer helpful?

    0 comments No comments
  2. George Hepworth 23,135 Reputation points Volunteer Moderator
    2020-01-25T21:06:16+00:00

    "An entity can simultaneously be of more than one entity type.  This is the case in a type hierarchy.  For example an employee can be of both Employees and Programmers entity types, where Programmers is a sub-type of type Employees."

    I think a good way to understand this is to think of the Employee and Programmer Entity Types as Role-based Entities. In other words, the same physical entity (person) can take on different roles in different situations or transactions.

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2020-01-25T18:36:53+00:00

    The word entity is often used incorrectly.  Each table represents an entity type, and each row in the table represents a valid entity of that type.  The entity types represented by tables are those present in the reality which the database is modelling. In a corporate environment, for instance, Employees constitute an entity type, and each employee is an entity of that type.

    An entity can simultaneously be of more than one entity type.  This is the case in a type hierarchy.  For example an employee can be of both Employees and Programmers entity types, where Programmers is a sub-type of type Employees.  Chris Date extends this example further with System Programmers and Application Programmers, each of which is a sub-type of Programmers.  A sub-type is characterized by sharing all the attributes of its (super) type but not those of other sub-types.

    An entity type does not necessarily have a physical presence in the reality.  This is true where an entity type is a relationship type.  Continuing the corporate example, a many-to-many relationship type between the entity types Employees and Projects is modelled by a table which resolves the 'binary' relationship type into two 'unary' relationship types like this:

    ProjectEmployees

    ….ProjectID

    ….EmployeeID

    ….StartDate

    ….EndDate

    The table is still modelling an entity type, because in the database relational model a relationship type is just a special kind of entity type.  This is even true of a unary (one-to-one or one-to-many) relationship type, which would not normally be modelled by a separate table, but by a foreign key in the referencing table referencing the primary key of the referenced table.  Conceptually the relationship type is still an entity type.  In some situations it is appropriate to model the relationship type by a table, e.g. where it is important to eliminate semantically ambiguous Null foreign keys.

    Each entity type has a set of attributes, each of which is modelled by a column in the table modelling the entity type.  In a correctly designed table each non-key attribute is functionally determined solely by the whole of the table's primary key.  This means that in any row returned by the database, e.g. in the result table of a query, for any value of the key the values of columns determined by the key will always be the same.  For EmployeeID 42 say, the values of the FirstName and LastName attributes will always be the same.

    Was this answer helpful?

    0 comments No comments
  4. Anonymous
    2020-01-25T18:34:03+00:00

    Hmm, I use Entities because the table contains people, companies, business' etc.  It is a primary table so always on the one side.  You can use Client or Customer I just prefer to be generic because such tables contain more then just people.

    Was this answer helpful?

    0 comments No comments