Ways to create a new Table in a DB?

D

Disasterpiece

Well-known member
Joined
Apr 2, 2003
Messages
47
Location
Blacksburg, VA
What I have is a program that creates a Database entry into a Users table - You create a login account and it adds that info to the User Table. I have an Autonumber ID as the Primary Key, and what I would like to do is create a new Table for each Autonumber ID - The program is a checking account program, so for each user I would like them to have their own new Table for transactions, and for the table of each user to be named whatever their ID Number is.

How would I do this? IE what is the syntax for creating a Table in a DB?

Thanks!
 
I must say I think this is a *bad* idea. There are *very* few scenerios (and I cant think of one right now) where the underlying data model should change dynamically. A very poor design in my humble (well kinda) opinion.

Maybe we could help you come up with a better design instead? In your case, maybe you could just add a foreign key (userid) to the transactions table.
 
The number of tables wasnt exactly what I was taking issue with -- it was the whole idea of dynamically creating a new table for each user, be it one or one thousand.
 
What I mean is I will probably go in and create 10 tables in Access and then reference them according to user in my code. So I dont think I will need to know what I was previously asking anyway.

Thanks
 
Ill expand my issue then to include your current solution;) I think its an odd, and one Ive never seen, situation where each user needs its own table. In fact, Id go so far as to say the data model is fundamentally flawed if thats the case. If you dont care, I suppose Ill live with it:) But as I said, we could probably help get it straight.
 
Well, I dont know how else I would do this.

The usernames Table stores things such as the Username, PW, bank name, starting balance, minimum balance, service charge, etc...

For each user in that table I will need a new table to hold their bank account transactions since that table will require different fields. Im limiting it to 10 (maybe even 5) users so I dont have to create so many tables for each user.

Hope this clears it up
 
Just from a basic understanding of your problem, I see three tables:

tblUsers (userID, firstname, lastname, pw, etc)
tblAccounts (accountID, userID, bankName, serviceCharge, startBalance, address, etc.)
tblTransactions(transactionID, accountID, ammount, endingBalance)

Then each user can have multiple accounts, which in turn have multiple transactions. To get all the users accounts would be something like:
select * from tblAccounts where userID=quwiltw
of course itd be even simpler if the user could only have one account, but that isnt clear in your problem.
 
Disasterpiece, you should really consider the solution provided by Tim (quwitw), or something similar. Creating a table specifically for a user should not even be considered in your design.
 
Its all coming clear now, that makes a lot of sense!

Thanks, Ill try that out :)

One more question though - Why not just put all of the account information in the Users table? (ie combine tblUsers and tblAccounts into one table).
 
Im only planning on allowing for one account (a checking account)at the moment, so would I be safe in combining the two tables then? This is just for a school assignment, I dont have the time or intent to make it a full fledged program hehe.
 
Back
Top