3.0 Data Modeling - germanngc/invoiceSender GitHub Wiki
It is important to define a good data structure model before start coding, this will help us to define a better structure for the data and the screens we will be programming.
3.1 Define the big picture
- Organizations
- Organization Branches
- Users: Records to allow users to access the application. This can belong to one or many organizations.
- Roles: The roles will be define the actions of each user inside the application.
- Customers: This customers whom shall receive the invoice, must be unique by organization.
- Templates: The design of the email that will be sent, each organization can have many.
- Invoices: The set of template and customer with the attachments to sent, this will be the actual email sent.
- UserAccesses: The track of login that users has.
- AuditLogs: Record the logs and changes on the CRUD.
- Attachments: The list of attachments that that belong to each invoice, this will be downloadable via link.
- Address Books
Catalogs, where data is commonly used and easy to manage
- Catalog Branch Types
- Catalog States
- Catalog Cities
3.2 Working on data models
3.2.1 Organizations
An organization is the main account which might be associated with multiple users.
3.2.1.1 Model
organizations
3.2.1.2 Data structure
| Column | Type | Default | Nullable | Extra | Description |
|---|---|---|---|---|---|
| id | biging(20) unsigned | false | auto_increment | The record identifier | |
| name | varchar(255) | false | The brand name of the org | ||
| legal_name | varchar(255) | false | The legal name of the org | ||
| legal_id | char(13) | false | unique | The legal id (rfc) | |
| created_at | timestamp | true | Date and time when record created | ||
| updated_at | timestamp | true | Date and time when record updated | ||
| deleted_at | timestamp | true | Date and time when record deleted |
3.2.2 Organization Branches
The branch of the organization, you can define as main or a branch.
3.2.2.1 Model
organization_branches
3.2.2.2 Data structure
| Column | Type | Default | Nullable | Extra | Description |
|---|---|---|---|---|---|
| id | biging(20) unsigned | no | auto_increment | The record identifier | |
| name | varchar(255) | yes | The name of the branch | ||
| street_1 | varchar(255) | no | The line address #1 | ||
| street_2 | varchar(255) | yes | The line address #2 | ||
| catalog_city_id | biging(20) unsigned | no | The id reference of the city | ||
| catalog_state_id | biging(20) unsigned | no | The id reference of the state | ||
| organization_id | biging(20) unsigned | no | The id reference of the org | ||
| type | enum('main', 'branch') | main | no | The type of branch | |
| created_at | timestamp | yes | Date and time when record created | ||
| updated_at | timestamp | yes | Date and time when record updated | ||
| deleted_at | timestamp | yes | Date and time when record deleted |
3.2.3 Users
The users which credentials need to match in order access the aplication
3.2.3.1 Model
users
3.2.3.2 Data structure
| Column | Type | Default | Nullable | Extra | Description |
|---|---|---|---|---|---|
| id | biging(20) unsigned | no | auto_increment | The record identifier | |
| name | varchar(255) | no | The name of the user | ||
| varchar(255) | no | unique | The email and login user | ||
| email_verified_at | timestamp | yes | The time when the email was verified | ||
| password | varchar(255) | no | The password encrypted | ||
| remember_token | varchar(100) | no | If application remember the session | ||
| created_at | timestamp | yes | Date and time when record created | ||
| updated_at | timestamp | yes | Date and time when record updated | ||
| deleted_at | timestamp | yes | Date and time when record deleted |