Hi,

What would be the best way (in terms of the tables needed and how many tables etc) to store licensing information entered into forms (web based or ms forms) by truck drivers? And allow reports to be made.

Any suggestions?

Well that depends on what licensing information you want to store, and what types of reports you want to make. Without knowing these things, the best answer I can give is 42.

The type of data that will be stored includes name and address of driver, when their license was issued, how long they have had their licesnce etc

What is the most efficient way to store this data? Would I have a driver table and then have a corresponding table that stores the license info separatly? And a separate table for addresses? Would I need any joining tables?

Thanks,

You're going to think I'm being an, well, it means the same as donkey, but the most efficient way to store data depends on how the data is accessed.

You could use one giant table instead of it sounds like you're considering 3. Now if you found your application editing one field that only corresponds to one functional area (the driver, say, or the license) that would be a bottleneck. There's a cost of having your data split into multiple tables, and it's paid when you join them. There's also a cost in having your data consolidated into single tables, which is paid in making updates, and usually in duplicated data giving you more volume to search through - incurring more physical, disc based IO to find a particular group of records. Optimizing a database for speed and efficiency is mostly about knowing what are the access patterns for the particular collection of data.

Again, I know that sounds like I'm trying to be unhelpful, but it's the truth. On the other hand, there's a concept of premature optimization. Most people will also say you're better off structuring the information in a way that makes sense, logically, and is easy to work with. Then, add speed improvements, if you find you really need to. At that point, you'll know exactly what part needs improvement, and why.

For simplicity's sake, I would start with one table since it all has a one-to-one relationship. Each driver only has one license. Unless you're wanting to keep past license information for drivers, then you would have to have multiple tables. Assuming you only need to keep current license information, just put it all in one table.

You won't suffer performance-wise until and unless you get to many thousands of records. Once you have thousands of drivers in your database and if things start slowing down, you can then decide based on how people are already using the database (the reports being run, etc) which data to split off into separate tables and which functions to build into stored procedures, etc etc.

Thanks again for the excellent comments Learning Newbie, your comment was informative and is helpful.

nyef, I would like to try and make the database design complex, this isn't for anything in particular, I just want to experiment and actually make a complicated database that conists of more than two tables lol

I have thought about other information that could be included, could you guys tell me how you would do it? I need other people's perspectives on this, (people with more experience in database design)

The system needs to store
driver name, address etc
license information
information on previous licenses
driving convictions
payments/fees

I'm guessing I could have tables for all of the above? A driver can have many payments, and convictions - one to many?

Table: t_driver
Fields:
id [int(4)]
firstname [varchar(50)]
lastname [varchar(50)]
address1 [varchar(200)]
address2 [varchar(200)]
city [varchar(50)]
state [varchar(2)]
zip [varchar(10)]
licensenum [varchar(50)]
licenseexpire [datetime]

Table: t_license
id [int(4)]
driverid [int(4)]
licensenum [varchar(50)]
licensedate [datetime]
licenseexpire [datetime]
state [varchar(2)]

Table: t_payment
id [int(4)]
driverid [int(4)]
date [datetime]
amount [numeric(9,2)]
description [varchar(200)]

etc etc

I always use a global unique identifier (ID or GID) for each table for indexing and faster loading of specific records. Then when you need to match up the data, you just join on the driverid field on the other tables and the id field on the driver table.

That help?

You can make it as complex as you want... you can even put specific restrictions on certain fields, triggers, etc... But the more restrictions you build into the database, the more time you will have to spend on database management. I prefer to make the database as unrestrictive as possible and let the web app handle all the metadata stuff. Database management is so boring =). I don't even usually bother preventing nulls on fields! Ha

Thanks nyef that's great you have answered many questions all in one post!

My last question is, where there is a one to many relationship do you need a joining table? Or is that only for many to many relationships?

Thanks,

You can have a one to many relationship with or without a joining (aka mapping) table. Let's take what you want to do for example, and look at both ways you could do it. We'll use drivers and addresses, including a history of addresses.

We could have a table of drivers which are really people. And a table of addresses. That table would have the city, state, zip code, and all the other address data we're used to. Now, you could add a field to your address table called (something like) DriverID, which would be a foreign key (FK) on the many side of a one to many relationship. Or you could not have that as part of the address, but have an AddressID field in that table, and a third table with DriverID and AddressID - maybe also a start and end date for when that person had that address. How I would decide which of those 2 options to go for, is probably to ask myself if, in the data model I'm building, 2 drivers can ever have the same address? That could be a husband and wife living together, or that could be a guy moving out of his apartment, and someone else moving in. Really, we're asking if it's a one to many or a many to many relationship. In the real world it's many to many, but can you ignore them for the purposes of your application?

Driving convictions would be another one to many. One driver has many convictions. It could be that Sam has none, and Bob has 5. Again, you could just put a DriverID field in the convictions table, which itself might point to a Crimes table. That would let you do a "roll up" and see how many convictions are for running a red light, versus how many are for DUI. In that case, you could think of your convictions table being a mapping table between drivers and crime types, except that it would probably have some other fields like the date, the sentence, and anything else that was useful.

I never use joining/mapping tables except for many-to-many relationships. On one-to-many relationships, just include the primary id of the "one" table as an extra field on the "many" table in addition to the "many" table having it's own primary id field.

t_driver
id
other fields

t_driverlicenseorwhatever
id
driverid
other fields

Then joins are easy:

select * from t_driverlicense,t_driver 
where t_driverlicense.driverid*=t_driver.id