database table design
- by e.b.white
I design the tables as below for the system
which looks like a package delivering system
For example, after user received the package, postman should record in system,
and the state(history table) is "delivered",and operator is this postman,
the current state(state table) is of course "delivered"
history table:
+---------------+--------------------------+
| Field | Desc |
+---------------+--------------------------+
| id | PRIMARY KEY |
+---------------+--------------------------+
| package_id | package_tacking_id |
+---------------+--------------------------+
| state | package_state |
+---------------+--------------------------+
| operators | operators |
+---------------+--------------------------+
| create_time| create_time |
+---------------+--------------------------+
state table:
+---------------+--------------------------+
| Field | Desc |
+---------------+--------------------------+
| id | PRIMARY KEY |
+---------------+--------------------------+
| package_id | package_tacking_id |
+---------------+--------------------------+
| state | latest_package_state |
+---------------+--------------------------+
Above is just the basic information to record, some other information(
like invoice, destination,...) should be recored as well.
But there are different service types like s1 and s2, for s1 it is not needed
to record invoice but s1 need, and maybe s1 need some other information to record
(like the tel of end user).
After all, at delivering way stations there are additional information to record,
and for different service type the information type is different.
My question is:
1. For different service type, shall I need to declare different tables(option A) or just
one big table which can record all information for all types(option B)?
2. If option A, since the basic information above is MUST, how
can prevent from declaring there duplicate fields in different tables?
