HELP! Create Table using SQL/Access/ ASP (Classic)

Greetings

Suppose I am selling tickets to an event.
The ticket includes BUS transporation to the event
Each bus has 56 seats

when one bus is full I need to create a new table for another bus

so..how do I "name" the table such that each name is unique?

For example (and point out SQL ERRORS please, im a newbie)

create table bus '<--------------- this must be in some sorta sequence to create different names
(busID autonum,
 bus_cost number(20), '<-----------   Question: should'nt this be a floating point number?
 num_seats number(2),
 bus_license varchar(10),
 city varchar(20),
 state varchar(20));

I hope I have made my question clear.
the_sleeperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pvginkelCommented:
Simple answer: you shouldn't. You should create an extra table with busses and use the ID of those rows as your unique identifier. Any system that uses creation of new tables as a standard practice should be looked at critically. Of course, if you want it this way, you can.

You could always use the current date/time as a name or keep a counter somewhere (in a table or a text file) and increment that every time you create a new table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hongjunCommented:
create table bus
(busID autonum,
 bus_cost number(20, 2),
 num_seats number(2),
 bus_license varchar(10),
 city varchar(20),
 state varchar(20));


I don't see the need to create multiple tables for different buses. You can use the same table.

busID = 1 represents first bus
busID = 2 represents second bus
...


hongjun
0
pvginkelCommented:
Yes, that's true. But in that case, how do you keep track of the seats. I take it you have a secondary table for that?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

the_sleeperAuthor Commented:


@ pvginkel >> You should create an extra table with busses and use the ID of those rows as your unique identifier

wow. that is a better implementation. Like this?

tbl_busses
------------
 bus_id
 bus_company
 bus_type
 bus_num_seats
 bus_cost
 bus_miles_per_gallon

which would mean I would need a table for seats (to keep count on max seats) so that I know when to enter a new record in [tbl_busses], right? wrong?

tbl_seats
----------
seat_id
bus_id
seat_count (guess this would be of type num)

Please advise

Thanks in advance
0
hongjunCommented:
tbl_seats
----------
seat_id
bus_id    <<< foreign key to tbl_buses

seat_id and bus_id together form composite key to tbl_seats
You do not need the seat_count since you can get it by the number of seat_id in a bus_id
0
pvginkelCommented:
No, not the seat count in tbl_seats. You want to store the seat number in there and a flag (an int which can get 0 or 1) whether the seat is sold. This would create 56 records in tbl_seats for every tbl_busses.
0
the_sleeperAuthor Commented:
@pvginkel >> You want to store the seat number in there and a flag (an int which can get 0 or 1) whether the seat is sold. This would create 56 records in tbl_seats for every tbl_busses.

You just lost me...whatyou have written is EXACTLY what im trying to do, but it's not ...."clear" yet...

what would the table structure look like? I dont "see" it.

@hongjun >> You do not need the seat_count since you can get it by the number of seat_id in a bus_id

I think you guys are saying the same thing, but I need an example to be clear
0
hongjunCommented:
>>You do not need the seat_count since you can get it by the number of seat_id in a bus_id

Suppose I have a bus_id value 1 and many seat_id from 1 to 50 for the same bus_id.
Using the below sql statement, I can get to know how many seats are there for bus_id = 1

SELECT COUNT(seat_id)
FROM tbl_seats
WHERE bus_id = 1
0
the_sleeperAuthor Commented:
@hongjun ...

ooohhhh! this is staring to make sense!

Now, to be even more clear :->....

I can use the SAME table for seats [tbl_seats] regardless of how many busses i need to create, right?
0
hongjunCommented:
yes..

bus_id of tbl_seats is related to bus_id of tbl_buses..

So this means you can have a bus_id in tbl_buses and that same bus_id may not even be in tbl_seats. This just mean there are no seats for that bus.


hongjun
0
pvginkelCommented:
You make the primary key a combination of bus_id and seat_id. Something like "primary key (bus_id, seat_id)". Then you can simply use the seat_id as the seat number.
0
the_sleeperAuthor Commented:
@pvginkel >> "You make the primary key a combination of bus_id and seat_id. Something like "primary key (bus_id, seat_id)".

In Access? (2003) i suppose... and then in SQL view only (i suppose).  


@pvginkel >> Then you can simply use the seat_id as the seat number.

How can the seat_id be the same as the seat "number" if a bus only holds 56 seats. What happens when the seat_id reaches 57?
0
the_sleeperAuthor Commented:
I just built it. I get it! you both were great...points comin...
0
the_sleeperAuthor Commented:
Now let me explain...

Thanks to pvginkel for correcting my wayward thinking in the early stages and pointing me toward the light. The concepts were clearly heard and 9finally) understood (mostly) ;->
 - "Simple answer: you shouldn't."

But also, shouts out to hongjun  for the patience and extra "newbie tenderness" showed...

"Suppose I have a bus_id ".....

aaawwww. wasnt that sweet? Made all the theory make sense.

Peace and blessings to you both! and thanks again.

I'd also like to thank my producers, directors and most of all you the fans!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.