Search code examples
sqldatabasedatabase-designerdairtable

Database design with ER diagrams

I started working in a start-up for a week now and I've been assigned the database side. I have no prior experience with databases and I'm studying as much as possible about them. We're using Airtable as our relational database. A week in, after some studying I realized that they think they're using Airtable as a database, but it's just basically a spreadsheet, because it doesn't respect any of the relational databases rules (no primary keys, data duplication without correct linking of records ecc.). But while trying to lay down an ER diagram for what will be our real database I got stuck, because CARDINALITY is confusing me.

  • We should have a marketing team that attracts customer and to keep track of how many customers each marketing person attracts.
  • Each customer will be offered three services: Share his books, Rent and read a book or request a book in case we don't have it in our database.

This is only part of all of it, but understanding this little part will probably help me grow this diagram.

This is the ER Diagram I've came up with at the moment.

Solution

  • Please consider the following:

    1. Your Marketing Team fit the mould of a customer, they are essentially a 'Person' Entity - and you are duplicating information unnecessarily.
    2. Each "Person" could have Role e.g. Customer or Marketing Team. The relationship between Entity and Role is 1 to many. So it would be possible for Marketing Team members to themselves be customers. 2B) This would suggest a mapping table of people and roles e.g. Person_Roles.
    3. I would suggest that you have a services table, that lists your services.
    4. You may find you need a table something like Person_Services that lists the services that a person requests - with timestamp details. So a person can request one or many services - and you can keep track of them. This table will have a field, say MarketingTeam_PersonId which will list the Person Id of the internal person who assists the person with his service request.

    This model will morph and change as you better understand the reality and what is not communicated to you while you put the puzzle together.

    Good luck.