Ways to create a new Table in a DB?

[Disasterpiece]

What I have is a program that creates a Database entry into a 'User's 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!

[Robby]

Is this SQL Server or Access?

 

For SQL Server use "Create Table myTable" & UserName

[Disasterpiece]

It's using Access

[quwiltw]

I must say I think this is a *bad* idea. There are *very* few scenerios (and I can't 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.

[Disasterpiece]

I think I'm only going to allow for 10 user accounts.

[quwiltw]

The number of tables wasn't 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.

[Disasterpiece]

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 don't think I will need to know what I was previously asking anyway.

 

Thanks

[quwiltw]

I'll expand my issue then to include your current solution;) I think it's an odd, and one I've never seen, situation where each user needs it's own table. In fact, I'd go so far as to say the data model is fundamentally flawed if that's the case. If you don't care, I suppose I'll live with it:) But as I said, we could probably help get it straight.

[Disasterpiece]

Well, I don't 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. I'm limiting it to 10 (maybe even 5) users so I don't have to create so many tables for each user.

 

Hope this clears it up

[quwiltw]

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 it'd be even simpler if the user could only have one account, but that isn't clear in your problem.

[Robby]

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.

[Disasterpiece]

It's all coming clear now, that makes a lot of sense!

 

Thanks, I'll 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).

[quwiltw]

To facilitate one user having more than one account (ie. Savings account, checking account, money market account)

[Disasterpiece]

I'm 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 don't have the time or intent to make it a full fledged program hehe.

[quwiltw]

Yep it'll work, just not the ideal design.