I'd go with option 3, which will require less overall work:
- Make a lookup table that contains a list of purged records and dates, like in your first option
- Rename your current table to something like
dbo.Tablename_master
- Create a
VIEW named dbo.Tablename which consists of:
.
SELECT <fields>
FROM dbo.Tablename_Master
WHERE PrimaryKeyField NOT IN (SELECT PrimaryKeyField FROM PurgeTable)
Now you don't need to modify any existing code, since all references to your original table go through the view instead. This will be complicated if you have any existing constraints but they should be easy to work around.
The decision does not depend on the number of columns you already have, but on whether those 5 columns do belong to what you are trying to model.
There is no rule that says: "if you have 10 columns, keep in same table; use a separate table when you reach 36 columns".
If those 5 columns are like a person's street, town, region, country, then no; they should be on a separate table "location" or similar.
If those 5 columns are like a person's 1st name, last name, 2nd last name, ID-number, and email address, then they should probably be there.
However, if your table already has 35 columns, then you probably haven't done much normalization to begin with and it looks like you're just stuffing everything on that table...
Update
first we have a table called association then based on that table we have the gps data linked to the assocition. In addition we also got alert tables linked to the association too. So association is the main table here. Each device also is table by itself but it will be linked to a association when and where it is being used.
…
Basically the association is just like a trip from a to b. So moving from a to b there is a number of gps data which is kept in this main table [?]. If in this trip there are alerts then is kept in the alert table. This 5 new column are indicating the status of the rfid devices which are linked to the main gps device. But in a trip not necessary to have 5 it can be less or even none too.
Assumptions:
- For each trip there can be 0..N alerts
- as I understand it, what you call association is the description of a journey
- multiple RFID devices are used per journey or trip.
- A GPS Device has 0..N RFID devices associated to it; in other words, each rfid device is associated to one GPS device (not sure I understand the logic of this, though; why are RFID devices used? what's the role of the GPS if you are )
- Given that you are using a GPS device, I assume there will be multiple readings during a Journey, in combination with RFID readings (?)
- When you say
moving from a to b there is a number of gps data which is kept in this main table, I'm not sure I understand what info is stored, or why it is stored in the table called association / journey instead of in some associative table (given that there seems to be "a number of gps data" and not just "one gps datum per journey")
Given this, I would add your 5 columns to the RFIDStatus table, since This 5 new column are indicating the status of the rfid devices. Below is a first attempt at modeling something similar to what you might be modeling...
[ Journey ]<——————[ Alert ]
| fk_from | | fk_trip |
| fk_to |
| |—(from)—>[ Location ]
| |–—(to)—–>| long/lat/name… |
| |
| |<—————[ RFIDStatus ]—————>[RFIDDevice]—————>[ GPSDevice ]
| | | fk_rfiddevice | | fk_gps | | |
| | | fk_journey | | |
| | | datetime | | |
| | | *status details* | | |
| |<———————————————————————————[ GPSStatus ]———————>| |
| | | fk_journey |
| | | fk_gpsdev |
| | | datetime |
| | | latitude |
| | | longitude |
However, as I mentioned in the comments, it all depends on what you're modeling. Please update your question with more details so other people can give you a better answer. Also, you should read something about database modeling so you start from a good basis and a good understanding of what's needed when modeling a database; it will also help you to ask better questions regarding database design.
Best Answer
The cardinality of 2 (male,female) is way too low.
Although you are using InnoDB and could have foreign keys, the parent table would have only two rows. It's not worth the processing power. Therefore, do not add another table. Indexing it is not worth it because a cardinality of 2 simply screams at the Query Optimizer "DO NOT USE THE INDEXES". Foreign Keys to a table with two values is not worth it.
Do not add a VARCHAR(1) because that is really two bytes.
There are four(4) options to use one byte