Recommended number of fields in a table - Computer Technical Help

Two Plus Two Forums Other Topics Computer and Technical Help

Recommended number of fields in a table

Post Reply Subscribe

...

08-18-2016 , 01:13 PM

StlGtrPlyr

enthusiast

Join Date: Jun 2011 Posts: 73

Is there a recommended number of fields for a database? I was wondering if I should use maybe two databases. I presently have ten as follows:

- Casino
- Location
- Date
- Game
- Cap
- Limit
- Buy_in
- Cash_out
- Time_in
- Time_out

Thank you;

Sherman

Quote

08-18-2016 , 01:33 PM

Loki

binary duo

Join Date: Nov 2015 Posts: 12,772

does this help any?

https://en.wikipedia.org/wiki/Databa...n#Normal_forms

Quote

08-18-2016 , 02:58 PM

StlGtrPlyr

enthusiast

Join Date: Jun 2011 Posts: 73

Quote

08-18-2016 , 05:53 PM

RustyBrooks

Carpal \'Tunnel

Join Date: Feb 2006 Posts: 24,647

Yeah that looks better.

One advantage is that your queries will be stored in less space and potentially execute faster. But the real win for most small sized databases is that it becomes easier to change your mind. Like if you decide that "Hold'em" should be changed to "Texas Hold'em" then you just change one row of your games table instead of every row that is currently "Hold'em"

BTW the split here is "tables" not "databases". Like what you have as "Database 1" is "Table 1" which I'd call something like "casinos" or "venues" or something like that.

Quote

08-19-2016 , 12:05 PM

StlGtrPlyr

enthusiast

Join Date: Jun 2011 Posts: 73

Thank you RustyBrooks. As I advance through the tutorial I see that I am dealing with tables within a database. I am thinking of calling the database games_of_skill since I will also include blackjack. I assume that all tables under games_of_skills can see each other as I have not gone that far into the tutorial yet.

Quote

08-19-2016 , 03:42 PM

RustyBrooks

Carpal \'Tunnel

Join Date: Feb 2006 Posts: 24,647

Yes, typically all tables within a database can be joined with one another. In some database systems, you can also refer to tables in other databases, if they're accessible by the user you're using.

Quote

08-20-2016 , 03:04 AM

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

StlGtrPlyr, what is your intention here? is it to create a well-designed database or is it more like a my first database, or a place to generally store data?

Also, what RDMS are you using?

Quote

08-22-2016 , 11:19 AM

StlGtrPlyr

enthusiast

Join Date: Jun 2011 Posts: 73

My first database and I am using it to store poker and blackjack information that I can pull and compute statistics. I am on Ubuntu only and presently using LibreOffice Calc to store the information. I had to look up rdms so now I know about that. I am using mysql.

I have learned how to create and drop a database. I have also learned how to create multiple tables under the database and how to insert information into a table.

My next step is to create three PERL stubs: create_db, drop_db, and insert_info (pr something like that). Then I will place the three stubs into one .pm file.

I have some old PERL scripts that I use to hand-enter information from calc and perform calculations. I will have that automated soon I hope.

Quote

08-23-2016 , 04:13 AM

_dave_

_Pooh_Bah_

Join Date: Feb 2005 Posts: 13,146

Since you're on Ubuntu and it seems you're new to databases, and intending to use this locally rather than on a website - I'd suggest using Postgresql rather than MySQL. It's better, and easy to install. Postgresql documentation is fantastic also (I imagine MySSL is decent in this regard also)

I'd normalize "Binnions" out in to it's own table.

Quote

08-23-2016 , 10:28 AM

#10

StlGtrPlyr

enthusiast

Join Date: Jun 2011 Posts: 73

Thank you, _dave_

Quote

08-23-2016 , 10:45 AM

#11

RustyBrooks

Carpal \'Tunnel

Join Date: Feb 2006 Posts: 24,647

I like postgres better too, but really, mysql is OK. Either is a pretty good place to learn the ropes.

Quote

08-23-2016 , 02:05 PM

#12

a12

adept

Join Date: Oct 2013 Posts: 825

If you are using MySQL you could also have a look at phpMyAdmin.
There is a demo available here: https://demo.phpmyadmin.net/master-config/
Note that you will also need a local httpd (Apache) server running.

Quote

08-23-2016 , 05:03 PM

#13

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

There's a huge difference between using a spreadsheet as a database and a bona fide database.

Those stubs and manual commands you are writing are easily done with in mysql.

Code:

select sum(colA) 
from my_table;

aggregates:

Code:

select colA, sum(colB) 
from my_table
group by colA;

To answer the thread question, normalization is a pretty counter-intuitive.

A relation is a table, but a table is not a relation. To have a relation, you have to have the following properties:

-- Each column must hold one value and must have one datatype.
-- Each column must be unique.
-- Each row must be uniquely identifiable.
-- Order does not matter.

A simple example, using your data:

Database 1:
- - Casino ID 1 | Binnions | Las Vegas
- - Casino ID 2 | Binnions | Louisiana

Code:

create table casinos (
       casino_id serial primary key,
       casino_name varchar,
       city varchar
       unique (casino_name, city)
);

This looks okay and follows all the rules of a relation, but there's a massive data integrity issue here:

Code:

insert into casinos
values
('binnions', 'Las Vegas'),
('binions', 'Las Vegas'),
('binnion''s', 'Las Vegas')
('binnions', 'LV');

If you are writing a script that auto-fills the casino and returns the id, every mistake you make will cause duplication errors (to the human, not the machine).

To prevent this, you would actually need to split this into more tables. I suggest this:

Code:

create table casinos (
       casino varchar primary key
);

insert into casinos
values ('binnions'); -- or whatever spelling you want

create table cities (
       city varchar primary key
);

insert into cities
values ('Las Vegas');

create table casino_city (
       casino varchar,
       city varchar,
       primary key (casino, city),
       foreign key (casino)
       	       references casinos (casino),
       foreign key (city)
       	       references cities (city)
);

-- this command will throw and error.
-- no values will be inserted:
insert into casino_cities
values
('binnions', 'Las Vegas'),
('binions', 'Las Vegas'),
('binnion''s', 'Las Vegas')
('binnions', 'LV');

-- this command will run:
insert into casino_cities
values ('binnions', 'Las Vegas');

Of course, you can change this around a bit. For example, you can add a serial to the casino_cities table and call it casinos instead. Perhaps call the casinos table valid_casino_names and the cities table valid_city_names, so you end up with this:

Code:

create table valid_casino_names (
       casino_name varchar primary key
);

create table valid_cities (
       city_name varchar primary key
);

create table casinos (
       casino_id serial primary key,
       casino_name varchar,
       city varchar,
       primary key (casino, city),
       foreign key (casino_name)
       	       references valid_casino_names (casino_name),
       foreign key (city)
       	       references cities (city)
);

insert into valid_casino_names
values ('binnions');

insert into valid_cities
values ('Las Vegas');

insert into casinos (casino_name, city)
values ('binnions', 'Las Vegas');

Last edited by daveT; 08-23-2016 at 05:09 PM.

Quote

08-24-2016 , 10:33 AM

#14

StlGtrPlyr

enthusiast

Join Date: Jun 2011 Posts: 73

Thank you. It will take me awhile to look at all this but it is good.

Quote

08-31-2016 , 06:56 PM

#15

StlGtrPlyr

enthusiast

Join Date: Jun 2011 Posts: 73

In a game_limit column and of the following entries which do you think looks best? I think I prefer the dot notation.

3/3/6/9

3-3-6-9

3_3_6_9

3.3.6.9

3 3 6 9

Quote

09-14-2016 , 07:13 PM

#16

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

In my previous post, I wrote:

Each column must hold one value and must have one datatype.

What you have done is NOT following this rule. You should have a table that looks a bit like this:

Code:

create table stakes (
   sb int,
   bb int check (bb >= sb),
   -- sorry, don't really know what the 
   -- other numbers mean, but...?
   pf_flop_bet int check (pf_flop_bet >= bb)
   turn_river_bet check (turn_river_bet >= pf_flop_bet)
);

but I'm sure you can figure out a better table design for this.

With all that said if you really need to use a field of values like you are suggesting, then see if MySQL has an array type. Well, it appears that MySQL doesn't have an array type, which is fine. I probably used array once in Postgres, and you definitely don't need it here.

Quote

09-15-2016 , 10:26 AM

#17

StlGtrPlyr

enthusiast

Join Date: Jun 2011 Posts: 73

Thanks

Quote

09-17-2016 , 03:01 AM

#18

daveT

S.A.G.E. Master

Join Date: Jun 2005 Posts: 23,955

So be sure, I'm not trying to be harsh or anything. Creating a schema that represents a collection of casino games would be difficult for me to do, and I'm pretty darn good at this stuff.

The best "tool of the trade" is a pen and paper. One of the many uses of Pen&Ink is E/R diagrams. It really helps to map all of this stuff out and get a firm visualization of what to do. This is an image so you can get an idea:

I'd suggest using pen and paper first then using the digital version of this. For E/R diagrams (and many other diagrams), I use dia diagram editor. It's FLOSS and definitely among my favorite programs: http://dia-installer.de/

Quote

09-17-2016 , 08:55 AM

#19

StlGtrPlyr

enthusiast

Join Date: Jun 2011 Posts: 73

Thank you. I do have problems visualizing each table and how to connect the tables and this will help. I was able to install dia on Ubuntu since it exists as a free install. Ubuntu also has LibreOffice draw but I haven't opened it yet.

Mapping this out will be a tremendous help.

Sherman

Quote

Post Reply Subscribe

...