November 26, 2009 at 5:43 am
Hi,
The second part of my development problem is (I think) far more complex indeed. I'll try and describe it in words first, provide some tables and then hope someone out there in SQL land is able (willing :-)) to come up with some workable suggestions!!!
a.One customer could have more than one EXPORT event (not really relevant here)
b.One customer can, of course, have more than one animal
c.In relocating animals, any number of animals can share 1 container.
1.Here's some animals that are being relocated:
--------------------
CREATE TABLE dbo.pets(petID INT, customerID INT, animalName nvarchar (25))
INSERT INTO pets values (1 , 1, 'Howl')
INSERT INTO pets values (2 , 1, 'Meow')
INSERT INTO pets values (3 , 1, 'Woof')
INSERT INTO pets values (4 , 2, 'Bark')
INSERT INTO pets values (5 , 2, 'Poop')
INSERT INTO pets values (6 , 3, 'Ruff')
--------------------
AS you can see
~ customer1 has 3 animals travelling
~ customer2 has 2 animals travelling
~ customer3 has 1 animal travelling
2.And here are the EXPORT shipments that will relocate them
--------------------
CREATE TABLE dbo.exports(exportID int, customerID INT, destination nvarchar(25), shipmentWeight decimal (18,2))
INSERT INTO dbo.exports values (1, 1, 'London', 0.00)
INSERT INTO dbo.exports values (2, 2, 'Trinidad', 0.00)
INSERT INTO dbo.exports values (3, 3, 'Beijing', 0.00)
--------------------
3.NOW, here's where it gets tricky AND I DON'T KNOW WHAT TO DO or HOW TO DO IT...
I must:
A.Capture in a grid, which pets will travel in which containers
B.Record in the DB details of ALL pets in EACH container
This must also be flexible so that the user can change which pet travels in which container.
Here is the basic container table that reflects:
export 1 (customer 1) has two containers - container 1 has 2 animals, container 2 has 1 animal
export 2 (customer 2) has one container - with 2 animals
export 3 (customer 3) has one container - with 1 animal
--------------------
CREATE TABLE dbo.containers(containerID int, exportID int, containerWeight decimal (18,2))
INSERT INTO containers values (1 , 1, 42.00)
INSERT INTO containers values (2 , 1, 32.00)
INSERT INTO containers values (3 , 2, 40.00)
INSERT INTO containers values (4 , 3, 32.00)
--------------------
Really friends, I am stumped with how to (a) capture the options and (b) record/process the options.
Any suggestions or advice will - be assured - be hugely appreciated and given credit whereever I can!
With appreciation and thanks in advance from a quite warm Dubai!
Nick
December 3, 2009 at 2:50 am
Several pets can travel in a container, but I suppose that a pet cannot travel in more than one container at once...:-)
So you have an "one to many" relationship between containers and pets.
Add a "ContainerID" colmn to the "pets" table. You can change the container by updating this column. To see all the pets in a container, run something like:
SELECT p.PetID, p.animalName
FROM Pets p JOIN Containers c ON p.ContainerID = c.ContainerID
WHERE c.ContainerID = ...
Maintain referential integrity through foreign key constraints.
December 3, 2009 at 2:54 am
...I suppose that you have limits regarding the number of pets that can travel in a certain container (depending on the pet, on the container, etc.....) - this could add complexity...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply