CODEWITHSUNDEEP
mysqldatabase-design
user3664587
user3664587

Reputation: 15

MySql database design: one big table or many small tables?

I am designing database and want to know what is best way to do it.

Let's say that i have table of users (with standard fields firstname, lastname, email etc.) and every user have balance in different currency (usd, eur, gbp etc). For every currencie there are couple of fields (for example available amount, reserved amount or something similar). There would be at least 30 different currencies.

It is posible that user would have data for every currency, but very unlikely. On average users would have 3-4 currencies.

Is it better to have:

a) One big table with colums for user info and all currencies.

b) One table for user info and one table per currency.

Database will be read and update heavy.

If I go with first approach, there will be a lot of empty fields which could make database very large (every field for currencies is DECIMAL(20)), but getting user balance could be done in one query.

If I go with second approach, I would populate certain currency table only if user have balance for that currency which would make database much smaller but to get total user balance I would have to make join for every currency table in one query.

Which option is better for best performance?

Upvotes: 0

Views: 1201

Answers (2)

user3664913
user3664913

Reputation:

I would suggest a table structure as follows:

  • First of all a User table with your {ID, FirstName, LastName} fields etc. For example you may have {1, Joe, Bloggs}

  • I would then use a look-up table called Currency containing the various types of currency with a unique ID to identify them. {ID, CurrencyName}. The data might look like: {5, 'GBP'}

  • A second look-up table may be used for AmountType {ID, AmountTypeName} where you would have data such as {1, 'Available Amount'} and {2, 'Reserved Amount'}

  • I would finally create a table to link User, Currency and AmountType using the IDs. This would look something like {ID, UserCode, CurrencyCode, AmountTypeCode} and you would have {1, 1, 5, 2} as your data, for example.

In this link table you have a unique ID to identify the record (1). As per the above example, the UserCode is 1 (Joe), the CurrencyCode is 5 (GBP) and the AmountTypeCode is 2, this translates to 'Reserved Amount'.

I hope you will find this a rationalised approach and that this helps.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270823

Neither of your options.

You want a table of users. You want a table of currencies. You want a junction/association table of balances.

The users table would contain all information about users that fits in one row. It would have an auto-incremented primary key, say UserId.

The currencies table would contain all pertinent information about a currency. That would probably be its name. It would have an auto-incremented primary key, say CurrencyId.

The balances table would link the two. It would have a UserId, CurrencyId, and other information about the balances, such as numeric quantities and dates.

Upvotes: 1

Related Questions