We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

the_sleeper
the_sleeper asked
on
Medium Priority
857 Views
Last Modified: 2011-09-20
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.
Comment
Watch Question

Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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

Commented:
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?

Author

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

Commented:
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

Commented:
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.

Author

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
Commented:
>>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

Author

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?

Commented:
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

Commented:
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.

Author

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?

Author

Commented:
I just built it. I get it! you both were great...points comin...

Author

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!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.