January 24, 2013 at 6:13 pm
Hello once again. have learned a lot from great minds on this forum. I would like for someone to look this over for me and see if this is correct.
I have the following Tables with info.
Table: Riders – This table stores all the info for each rider like ( Name , address, phone and other fields). I have Set a PK Field to RIDER_NO, int not null) increment 1
Table: Med_Provider – This table stores the contact info for a DOCTORS, HOSPITAL, such. ( Name , Address, Phone and such. I have set a PK to MedProv_NO. increment 1
Table: TRIPS – this stores the info for each trip that takes a person or persons to and from a doctor, hospital or any other medical service. There could be 20 people riding on 1 bus for example which is 1 trip. I have the following fields in the TRIPS table.
TRIP (PK, int, not null)
TRIP_DATE (DATE, not null)
RTF (nvarchar(5) , null)
LIFT_RAMP (nvarchar(3), not null)
AREA (nvarchar(3), not null)
TRIP_DESC (nvarchar(50), null
NOW HERE IS WHERE I HAVE PROBLEMS NORMALIZING
So a trip can have many riders on it, so I setup the following table so each person who rides on the trip could be entered .
TABLE: TRIP_DETAILS
TRIP (FK, int, null) reference TRIPS(TRIP ) question should I add a new field say TDNUM as PK?
RIDER_NO (FK, int, not null)
LAST_NAME (nvarchar(20), null
FIRST_NAME (nvarchar(20) null
MEDPROV_NO (FK, int, not null)
PROV_NAME (nvarchar(30) not null)
APT_TIME (time(7) , not null)
PKUP_TIME (time(7), not null)
NOW
Once the Trip has been created , and the people riding on this trip has been entered in the TRIP_DETAIL
Table a tripsheet is created for that trip and given to the bus driver and he has to fill in data by hand.
After the trip is made the tripsheet that is printed with the info from the TRIPS and TRIP_DETAIL tables
Are returned the next morning . This data is where I have a problem on deciding were to put it. I did setup a new table to store the data, but I am not for sure if I need this table.
TABLE : TRIP_STATS
TRIP (FK, int , not null ( same as above If I need this table question should I add a new field say TRP_NO as PK?
BUS_NO (int, null)
ADULT_RIDERS(int , not null)
NON_ADT_RIDERS(int, not null)
ADULT_ELDERY (int, not null)
ADULT_HNDY_CAP(int ,not null)
NUM_STOPS(int, not null)
Could I put theses fields in the TRIPS table, and the user could enter the data and do an update on the trips table. In other words these fields would reside in the TRIPS table they just would not be entered until the next day. I hope I am making since.
Thanks as always.
January 24, 2013 at 11:12 pm
i will suggest to keep the TRIP_STATS table separate from main table TRIPS ans TRIP_DETAILS
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply