Database Design Help

[markstaylor]

Need help in getting my head around this.

I need to create a database to do the following.
Here is the database information

Locations
-name
-price

Price will change daily, the name may get renamed, deleted, added.

Customers
-custname
-email
-etc

I need to be able to assign multiple locations to a customer and the price associated with that location. I also need to ensure that if a location has been removed, renamed it reflects in the customers information.

Not sure how to link this data together.

[Dermot]

You will need 3 tables.

1. Customers
2. Locations
3. CustomerLocations

There will be a one to many relationship between the Customers table and the CustomerLocations table.

There will also be a one to many relationship between the Locations table and the CustomerLocations table.

The CustomerLocations table will need at least 3 fields. If you need a quantity field for example, you would add it to this table.
1. CustomerLocationID
2. CustomerID
3. LocationID

When yoiu need to view the locations for a customer, just do a SELECT... JOIN statement.

When you delete a location, you will have to delete all matching records from CustomerLocations. DELETE FROM CustomerLocations WHERE LocationID = '"..LocationID.."'" If you are using SQLite, you can setup a trigger to do this automatically. If you are using MS Access, you can use relationships to do this automaticaslly also.

[markstaylor]

Thanks.

Can you dumb that down even further for me? Actual data fields may help me.

So I have a customer A
I have a location 1 and 2


I create a customer table (which will have A)
I create a Location table (which has 1 and 2)

and if I want to associate customer A with location 2

I create a customerlocation table that has
CustomerA2 as the entry

and if I add three more locations added and wanted to add location 4 to the customer A
I would add an entry for customerlocation table as
customera4

Then if I want to list all the locations that customer A uses
I would search the table for "custname" in customerlocation table?

I guess I dont understand how the fields would be setup in the customerlocation table.

I would be using the sqlite plugin and I'm not fimiliar with the triggers you mention.
?

[Dermot]

The customer table should have a CustomerID filed. Same for Locations, it should have a LocationID field. These will be used in the CustomerLocations table to link them all together.

Do not store duplicate data in the CustomerLocations table. For example, do not store the customer name or location name. You can pull these from the customer and locations table.

The CustomerLocations table will have one entry for each location for each customer. So if customer A has 2 locations, then there will be 2 entries with that cusomer ID in the CustomerLocations table.

To view all locations for a customer, you would search the CustomerLocations table for the customers ID, not name.

Here is an example of how to create a trigger.


SQLite.Query(db, "CREATE TRIGGER LocationDelete AFTER DELETE ON
Locations BEGIN DELETE FROM CustomerLocations WHERE LocationID = Old.LocationeID;

Once the trigger has been created in the db, it will fire when ever a location is deleted, and it will delete all matching records from the CustomerLocations table.

END")