January 17, 2013 at 1:26 pm
I am trying to write a program in vb.net that has a master/detail form.
I have 2 tables I need to set relations and I have read articles on this and sql book I still donot get it. I have tried to set the relations on these 2 tables, but when I set up my master/detail form. I only get 1 field from my detail table and that field matches the field in the master table (TRIP).
Tables:
The 1st table is called TRIPS their can only be one record for each TRIP_NO is this table: and this TRIP_NO can be in the TRIP_DEATILS table
Many times because several people have riden on the trip, so if 5 people have ridden on this trip I would have 5 of the same TRIP_NO 1 for each of the 5. in my TRIP_DETAILS TABLE. I also have a Riders Table, Medical Providers table, And a TRP_STATS table.
TRIP (PK, int not null)
TRIP_DATE (smalldatetime, null)
MONTH (varchar(8), null)
BOOK (narchar(8), null)
AREA (narchar(5), null)
RTF (narchar(5), null)
MCT_NO (narchar(10), null)
TCC (narchar(5), null)
LEG (narchar(5), null)
PASS (int , null)
WCP (narchar(5), null)
TDECS (narchar(50), null)
TCOMT (narchar(30), null)
BUS_NO (int, null)
-----------------------------------------------
TRIP_DETAILS this table has many TRIP_NO, because A trip can have many people who ride on the same Trip.
This table stores all the information for each person who have riden on this trip.
TD_NUM (PK, int, not null)
TRIP (FK, int, not null) "FK set to TRIPS TABLE "TRIP"
RIDER_NO (int, not null)
LNAME (narchar(20), null)
FNAME (narchar(20), null)
MEDICAL_PROV (int, null)
APT_TIME (time(7), not null)
PKUP_TIME (time(7), not null)
NEW_RIDER (narchar(3), not null)
Thanks
--------------------------------------------------------------------------------
January 18, 2013 at 11:21 am
COULD SOMEONE PLEASE LOOK AT MY 1st POST. Please tell me if the table structure is a one-to-many.
Thanks
January 18, 2013 at 11:42 am
is there data in the other fields of your detail table and ur app is not pulling it back? or is there no data for all the other columns in the detail table?
January 18, 2013 at 11:58 am
lonhanner (1/18/2013)
Please tell me if the table structure is a one-to-many.
Yes, it is a 1-n relationship.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 18, 2013 at 1:36 pm
I'm not sure what your requirements are, but here's a suggestion. If it isn't applicable to what you're trying to do, simply ignore it.
If you can have one trip with many people on it, can you also have one person who has taken many trips? If so, so you want a normalized table of people? So, you would be creating one table for Trips, one table for People and one resolve table between them. So, you would have Trips --< TripPeople >-- People. That is one-to-many and many-to-one.
This is just a design possibility. Like I've read in someone's signature on this site, failing to plan is planning to fail. I'm not sure who's tag-line it is, but they're right. If the system grows much, you'll want a unique list of people (customers) and then to be able to pull up the trip history for any person. Given the topic, this system is one I think is very likely to grow. I don't want to see you have problems having to rework things later. Just food for thought.
January 18, 2013 at 1:41 pm
Thanks for responding
I am not for sure what your question is . I farly new at this.
Yes I have data in the TRIP_DETAILS TABLE. YES I HAVE DATA IN MY TRIPS TABLE.
January 21, 2013 at 5:35 am
I was asking if you wanted to create a master customer list. Without one, if John Smith takes a trip to Italy and then takes a trip to Spain, you would have his name and information twice in the TRIP_DETAILS table, one for each trip he took.
In a situation where you have a master customer list, you'd have his name in CUSTOMERS once and two rows in the resolve table between them.
TRIPS would have information about the trip.
CUSTOMERS would have information about the customers.
TRIP_DETAILS would have a row with a foreign key to TRIPS and a foreign key to CUSTOMERS.
This way, the many-to-many is resolved by the resolve table CustomerTrips. One trip can have multiple customers and one customer can take multiple trips. Does this make any sense at all? I'm not the best at explaining things like this...it would be better if I could draw it out. 🙂 Maybe this SQL will help to illustrate what I'm trying to say.
create table Trips (
ID integer not null identity (1, 1),
constraint trips_pk primary key (id),
Destination varchar(64));
create table Customers (
ID integer not null identity (1, 1),
constraint customers_pk primary key (id),
FirstName varchar(64),
LastName varchar(64));
create table CustomerTrips (
ID integer not null identity (1, 1),
constraint customertrips_pk primary key (id),
CustomerID integer not null,
constraint CustomerTrips_Customers_FK
foreign key (CustomerID)
references Customers (ID),
TripID integer not null,
constraint CustomerTrips_Trips_FK
foreign key (TripID)
references Trips (ID));
January 21, 2013 at 8:54 pm
Thanks very much ED the example you provided done the trick
lon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply