Tables in a database query?

Question

I am designing a database and I am putting together my tables. I am using Visual Studio 2008 Express, VB.Net and SQL Server 2008 Express. I am having problems with structure and relationships. These are my tables:
new_veh_tbl
veh_reg(primary key) depot_id chassis_number year make model mileage veh_notes

inspection_tbl
Veh_id(primary key) comp_mot_date comp_tax_date comp_loler_td comp_twoyr_td comp_sixyr_td

inspec_tbl
Veh_id mot_dd tax_dd loler_test_dd 2yrtaco_dd 6yrtaco_dd notes

history_tbl
Veh_id service_date service_item labour_hours labour_rate invoice_no notes service_cost total_servicecost_todate

Basically I want the user to click a button and perform a search on the databse. I want the user to be able to see records of a vehicle inspection in date order. I will build the SQL query for this.

The problem I have is understanding how I can store the information in a table. Basically a vehicle has to have an annual mot inspection. I allow for this in my current table that has a field for the next inspection date and the last date of the inspection.

Could I incorporate an archive table that holds all of the previous completed mot dates? The problem is that I am unsure of a primary key that this table would use? Does a table have to have a primary key?

Hope you can help. Or if you could point me in the right direction.

Answer 1

An archive table is a good idea. You do not need to have a primary key. However, you should define an index on the archive table so that your queries are fast to retrieve data (hint: look at your where clause to determine the best field/s for indexes). Potentially, a foreign key could be defined on this table.

If this is a school exercise, you do not need to worry about anything else. If this is an industrial application, you need to have a business rule to determine how long data will be archived and have a routine to clean up the table at regular intervals to prevent it from growing to a very large size with lots of data that will never be used.

Good luck!

Answer 2

The table doesn't have to have any key at all, but searching a large table with no key can take a long time.



You can store inspections in a table of vehicle_id and inspection_date. The last date (in date order) in the past would be the last inspection date, the date in the future would be the next inspection due date.



I'd index on both vehicle_id and date, since you may be searching on both. (A key can consist of more than one field.)

Answer 3

Theres a minimum of two the form to do it. a million. a query is a table (probably various) containing a series of fields from the source tables and calculated fields. Use the question basically such as you will possibly the table, in varieties, comments. 2. carry out an replace so as that the VAT field interior the uncooked table is overwritten with the calculated value. In the two case, i might propose a VAT value code field for the products. e.g. CODE=a million for no VAT, CODE=2 for traditional value and so on. And in straightforward terms have the 17.5% in one place somewherein the database, so if it has to get replaced (it those days went to fifteen%) its ineffective uncomplicated. in case you had as much as date the database in technique 2, you will possibly want to circulate by way of all documents and adjust the calculation. technique a million is extra of a "stay" gadget.