is this table is not good or not normalize

[Samanthi perera]

i need to make a tabel in database using these collums(Zonetype zone peak offpeak cash).
I make a one table using these things .anyway i understood this table is not correct.
because i can't show number of tickects available for each types of zones?
I mean it is not normalize or any other error.

actually you may be can't understand what are these colum type.
i will explain.
1)this is a ticket system.
2)there are 4 zone types.
3)each zone types has many zones.
4)you can pay for tickets in credit card or cash
2)if you pay credit card you can select peak or offpeak
but is you pay using cash there is no any peak or offpeak

so i make a one table with these colums(i gave example values too)

Zonetype  zone   peak  offpeak   cash
1               a         2       1            4
2               b         3        2           5
.......................................................

but the problem is i can't show the number of tickects available for each zonetypes with peak,offpeak or cash??
how to solve this issue?

[Shailesh Chandra]

Samanthi perera wrote:

3)each zone types has many zones.

I will give only one suggestion, as your table design is not correct. you need to consider relationship among your columns.

if each zone type has many zones which means there is one to many relationship between zonetype and zone, so zonetype should be in one table let say as primary key and zone should be in other table with a foreign key to zonetype.

hope this helps.

Shailesh

[Samanthi perera]

it is ok.
we can remove the zone colum.
but the problem is same zone appears in different zonetypes.
i have attach a screenshott too.

But the other problem is that each zone have value for peak,offpeak and cash .
so if we remove them how to manage peak,offpeak and cash columns?

[Samanthi perera]

screenshot

[Shailesh Chandra]

All I wanted to say is one to many relationship should be handled using 2 tables, you need not to remove any column.

if I go back to square one, your problem is to find number of tickets available, but none of your table structure is having such columns.

Shailesh

[Samanthi perera]

ya.i ma having how to add that colume to table?

[Shailesh Chandra]

tickets availability is bound to "zone" or "zonetype" ?

[Samanthi perera]

hi Shailesh Chandra ,I sent you a private message.Please chaeck.

[Shailesh Chandra]

Hi Samanthi,

I am sorry if I am being rude, but In my view the purpose of javaranch is to help people to solve the problem, also the people who read this thread may be able to extract something to solve their problem.

moving the communication to PM's will not help those people who will read this thread to solve their problem.

Shailesh

[Samanthi perera]

ok
i will post my requirment here

Transport for London (TfL) is the local government body in charge of most aspects of transport
system in Greater London of England. One of its main roles is to manage the transport services
across London. TfL is organised in three main directorates and corporate services, each with
responsibility for different aspects and modes of transport. The three main directorates are:

• London Underground, responsible for running London's underground rail network, commonly
known as the tube.

• London Rail, responsible for:
o Coordination with the operators that provide National Rail service within London.
o London Overground.
o Docklands Light Railway (DLR), this is the automatically driven light rail network in
East London.
o London Trams.

• Surface transport which consists of a number of independent services such as London Buses,
London River Services, Victoria Coach Station (which operates London’s principal terminal for
long distance buses), Cycling Centre of Excellence (which promotes cycling in London) and
etc.

There are six travel zones in London which are circular areas with zone 1 (covers the most central
part of city London) at the centre. Zone 1 is surrounded by zone 2 which is surrounded by zone 3 and
so on. See the map at http://www.tfl.gov.uk/assets/downloads/oyster-rail-services-map.pdf for some
idea of underground/rail network and the zones.

Most of the transport modes under the control of TfL have their own charging and ticketing measures.
For instance, buses and trams share a common fare and ticketing measure while the DLR and the
Underground share another. Superimposed on these mode-specific measures is the Travelcard
system, which provides zone-based tickets valid from one day to one year. These are accepted
widely and provide great discount.

The Oyster card is a contactless smart card system that can be used to pay individual fares (Pay As
You Go - PAYG) or to carry the various Travelcards. It is used by touching the card on a card reader.
Card readers are on buses and tube stations where a valid ticket is required for travel. Oyster cards
provide a convenient and cheaper way for travel in London.

The following is an extract from the Transport for London guide to fares and tickets within zones 1–6
(valid from 2 January 2009 until further notice)