Purpose
Effective database design is crucial for building reliable, efficient, and scalable data systems. It involves steps like requirements analysis, conceptual and logical modeling, normalization, physical implementation, and ongoing maintenance. A well-designed database ensures data integrity, reduces redundancy, and optimizes performance. It also supports security, scalability, and ease of maintenance. By following structured design principles—such as using ER diagrams, enforcing constraints, and planning for future growth—organizations can prevent data anomalies, improve query efficiency, and make better business decisions. Ultimately, good database design is foundational to the success of any data-driven application or system.
Summary
This is a sample blog post. Lorem ipsum I can’t remember the rest1 of lorem ipsum and don’t have an internet connection right now.
Methodoloy
Description: Stores all completed customer transactions.
| Description | Type of tasks | Purpose |
|---|---|---|
| order_id | INT | Primary |
| customer_id | INT | FK |
| order_date | DATE | Date |
| amount | DECIMAL | Total |
Notes:
- reason = my own decision based on business acumen
- Use
order_dateitem
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
/* Question 1
movenmoviesmini schema.
What do we notice about it? How many table are there? What does the data represent? What do we think about the current schema?
*/
/* Question 2
If we wanted to break out the data from the inventory_non_normalized table into multiple tables, how many table do we think
would be ideal? What would we name thise tables?
*/
/* Question 3
Based on our answer from question 2, create a new schema with the tables we think will best serve this data set.
We cna use SQL code or workbench UI tools (whichever we feel more comfortable with).
*/
/* Question 4
- Next, use the data from the original schema to populate the tables is newly optimized schema
*/
- Q4
NEXT INTO inventory (inventory_id, film_id, sotre_id)
SELECT DISTINCT inventory_id, file_id, store_id
FROM mavenmoviesmini.inventory_non_normalized
SELECT * FROM inventory;
INSERT INTO film (file_id, title, description, release_year, rental_rate, rating)
SELECT DISTINCT file_id, title, description, release_year, rental_rate, rating
FROM mavenmoviesmini. inventory_non_normalized
SELECT * FROM film;
INSERT INTO store (store_id, manager_first_name)
SELECT DISTINCT inventory_id, file_id, store_id
FROM mavenmoviesmini.inventory_non_normalized
/* Question 5
Make sure our new table have the proper primary keys defined and that applicable foreign keys are added.
Add any contratints we thing should apply to the data as well (unique, non-NULL, etc>)
*/
/* Question 6
Finally, after doing all of this technical work, write a brief summary of what we have done, in a way that your
non-technical client can understand. Communicate what we did, and why our new schem design is better.
*/
Reference
Footnote
The footnote source ↩︎