Tables (1 Viewer)

[Pauldohert]

If I had a db that needed to record vasltly differant services offered to cutomers

ie

hire of equipment
sale of goods
attendence on a training course

if I was making a database for each of these separately no doubt the booking table for each service would look very differant as very differant info is need for each service.

If I was making a db with all three of these as part of the system - would I stay with 3 differant booking tables or somehow combine them into one?

 

[Pat Hartman]

Leaving this as three separate tables wouldn't be wrong but you might find in the future that you would like to be able to better group this information. You could use a technique that I have used several times when confronted with the problem of customers who may be individuals or companies or having to deal with employees as well as contacts in the same appliation.

Create a products table. Use an autonumber primary key and store all the common fields in this table such as the name of the product and its cost. Include a type field which indicates which type of product this is. Then create three other tables with the individual attributes required for each product type. The key to these tables will be a long integer and it will also be the foreign key back to the products table. This will create three 1-1 relationships - don't forget to enforce RI and make sure it goes in the correct direction or you will be really confused. Since the relationships are sparse (not all products will appear in all three tables), use LEFT joins when joining the product table to each of the product details tables.

 

[Pauldohert]

Thanks Pat.

 

[TaiChi56]

I would make each of your examples a table. So you would have "Hire of Equipment" one table, "Sale of Goods" and "Attendance on a training course" each their own table. You would then link them as needed. For example, "sale of goods" would have a relationship to your customer table, attendance on a training course would have a relationship to your employee table, so forth.

I would suggest using an E-R diagram and draw it out before starting your application. Hope this helps.

If I had a db that needed to record vasltly differant services offered to cutomers

ie

hire of equipment
sale of goods
attendence on a training course

if I was making a database for each of these separately no doubt the booking table for each service would look very differant as very differant info is need for each service.

If I was making a db with all three of these as part of the system - would I stay with 3 differant booking tables or somehow combine them into one?