I need a high-level database/table/file explanation

[Kaido]

I'm trying to understand basic database structure. Let me see if I got this right:

1. A database is a single file in the system.
2. A table is like a spreadsheet (with rows and columns) within that database.
3. You can have multiple tables within a database, but you still only have one database file in the system.

Is that right or am I missing it completely?

 

[Drakkon]

almost...you CAN have mutiple databases per system...a lot of DB systems i work with have had a couple...a main and then a development mostly...

each database will run in a single file but its not like that file can just be picked up and transfered between machines easily...

you are correct on the table thing...tables reside within databases and are defined by their columns, each row is a record....but all in all prettymuch like a spreadsheet...

 

[statik213]

I'm assuming you're talking about relational databases systems....

1. A database does not have to be one single file. Access uses a single file for each database whereas mysql uses an entire directory for each database with seperate subdirs for each table and seperate files to hold indices and actual data.... Other databases can take various approaches...
i could be wrong but I thnk Oracle uses it's own filesystem to store it's databases...which makes a lot of sense 'cos a database is in a sense a file-system and having two layers of logic to find a given bit of information is redundant.....

2. A table is similiar to a spreadsheet in that it has a variable number of rows and a fixed number of columns --- i.e. you define the type and number of columns and don't usualy change them (unless your data organization changes).

3. Multiple tables belong to one database, not one file (see 1). You can have multiple databases per system as well, and you can have multiple database engines hosting multiple databases per system as well. i.e. an MSDE server with several databases, several Access databases, several MySQL databses and any other combo you chooose.

 

[Kaido]

statik213, for clarification, what I meant by one file was one system file. Multiple tables can be contained within one database, and that one database is a single file in a system such as Windows or Linux.

More questions:

1. So, I could have one database, and within that database, have a sales table, product table, order table, etc.?
2. Is it possible to access multiple databases at the same time? Say I'm running MySQL on a server and I have a PHP frontend and I want a report from both a Sales database and an Ordering database. Can I pull info from both and display it on a single page quickly?

 

[Drakkon]

Well each DB system is different...
Oracle has one set of files for each databse that reside in a databse directory, similar to mySQL
while like said above MSACCESS just has the one mdb file
Other databses can be contained in one file, two files, multiple files, or run as a memory process and never get stored on a system

for the more questions:
1. that is the idea behind databses, to have multiple tables all linked together under a common theme...so the product table has unque prodecut ID's that when ordered go into the order table. thus linking products to orders
2. you shouldn't want to have different databses for sales and ordering if they are related in some way, but it can be done, you just have to setup seperate connections to each databse and just store off varaibles and query 1 at a time...why you would want to do that though i couldn't imagine a reason why. Its always better to just have one databses that houses multiple tables all relating to the same system

 

[Mark R]

1. A database can be one or more files. Many databases management programs will use a single file to enclose a complete database with mutliple tables. E.g. Access always uses a single file for a database.

However, if it is multiple files, then the whole group has to be treated as one unit. Deleting, or damaging, a file may make the whole database unusable.

[ More complicated stuff - skip if desired ]
In addition to the examples described above, some high performance databases allow you to put different tables in different files, for performance or backup reasons. E.g. you might have a table with 100,000,000 records which takes 40 GB of space - backing this up could be a problem, so some databases allow you to split a table into 2 files for easier handling (E.g. if it's a list of phone numbers - numbers starting 1-5 could go in one file, and numbers starting 6-9 could go in another). Alternatively, you could put each table in a different file, on a different drive. E.g. A phone co could have the customer details table on one drive, the billing and account detail on another, and the phone records on another. On this system a query to bring up a customer's account status could be run in parallel - kind of like RAID.

2. A table is like a spreadsheet in that it is a grid. However, in a spreadsheet you have 'cells' which are arranged in rows and columns. Each cell is independent from every other one, and each cell can contain any type of data.

A database table has a fixed format chosen when the database is designed - e.g. 5 columns - 1st column - ID number, 2nd column - Name, 3rd column Address, etc. You can only put appropriate types of data into the database - e.g. you couldn't put text into the ID number field. Similarly, a database row is the minimum piece of data that you can put in a table. In a spreadsheet, you could put data into any cell you want. However, in a database table, you must first add a row, and then put the data into that row.

Additionally, spreadsheets allow calculations between cells, and any cell can access any other cell in the sheet. In a database, you usually can't perform calculations within a table - you need to run a query to extract the data and perform the calculations.

3. See above. It's common to use only a single file for the whole database, but not essential.

Additional questions

1. Exactly

2. You could, but if data is related it's best to put it all in the same database. Sales and orders essentially measure the same sort of thing, so it's best to put them in teh same database. You can than define relationships between tables so that linked information can be pulled out in one go.

E.g. you could have a table which contains a list of your customers. The orders table then has space for a customer ID. By defining a relationship between tables, you can have the database automatically, and very quickly, pull out customer name, address, etc. without the same data being repeated over and over in the orders table.

Seperating data into different databases subverts this relationship process, so you would't have this highly optimised linked data retrieval system.

 

[Kaido]

How much data can each cell/field hold? Can you type paragraphs of information? For example, if I were to make a product table, and I wanted it to have the following columns:

Model Number | Quantity in Stock | Quantity on Order | Description | Engineering Comments

And Description was a paragraph-long description and Engineering Comments was 2-pages long...can I do that in a database?

 

[Kaido]

I think I'm going to start by learning SQLite (flat-file) and then move up to PostgreSQL (relational). I've heard MySQL is fast, but lacks some of the capabilities of PostgreSQL.

 

[ttown]

Originally posted by: Kaido
I'm trying to understand basic database structure. Let me see if I got this right:

1. A database is a single file in the system.
2. A table is like a spreadsheet (with rows and columns) within that database.
3. You can have multiple tables within a database, but you still only have one database file in the system.

Is that right or am I missing it completely?

Lose the idea that a db is a single file. High-level concept: "a database is a collection of tables"
A table is typically like a spreadsheet, w/rows and columns -- but more advanced features can complicate this basic concept a bit.
Typcially, the tables in a database are related to a general system -- like "people, addresses, and phone#'s" or "purchasing, inventory, sales, billing". That's a simplified idea. Implementing a "purchasing" system can be quite involved and encompass many many tables, just for that one system. Then add-in all the tables needed for inventory, sales, etc. -- and then implement relationships between all the different systems.

simple example: Contact-Book
might have 3 tables; PersonTable, AddressTable, PhoneNumberTable
A person could have multiple addresses, and multiple phone#'s.... so there would be a link between one PersonTable record linked to maybe many AddressTable records. Same idea with phone#'s.

The tables might have fields like:
PersonTable:
personID (a unique #) --> points to maybe several records in AddressTable; also PhoneNumberTable
personFirstname, personLastname
personBirthDate
personType (friend, family, business)

AddressTable:
personID (to relate the PersonTable to AddressTable)
addressLine1, addressLine2, addressCity, addressState, addressZip
addressType (home, billing, shipping)

PhoneNumberTable:
personID (to relate the PersonTable to PhoneNumberTable)
phoneNumber
phoneNumberType (cell, work, home)

Even this simple example has some flaws that I'm ignoring (like, multiple work#'s; a business contact who is also a friend, etc)... but i think shows the idea.

 

[Drakkon]

Originally posted by: Kaido
How much data can each cell/field hold? Can you type paragraphs of information? For example, if I were to make a product table, and I wanted it to have the following columns:

Model Number | Quantity in Stock | Quantity on Order | Description | Engineering Comments

And Description was a paragraph-long description and Engineering Comments was 2-pages long...can I do that in a database?

You can do long paragraphs in a databse so long as you make it a column that can support that type of data. With SQLlite a text area can be unlimted in size, but with other DB's you deine how big the field is and sometimes it can't be stored off in a regular text field and thats when you start moving into BLOBs (Binary large objects that can store LARGE ammounts of data, talking gigs here).

I think I'm going to start by learning SQLite (flat-file) and then move up to PostgreSQL (relational). I've heard MySQL is fast, but lacks some of the capabilities of PostgreSQL.

I've started playing with SQLite and all i can say is it might not be the best if your trying to create a fancy order system. Its good for storing data, but not good if your going to do any real manipulation. I'd say mysql is a good starter DB because there is a great front end with phpMyAdmin...moving to posgre from that could be an improvement but once you get used to mysql (especially the newest incarnation) it has more than enough functionality than most people would ever use.

 

[Kaido]

Wow, real databases look like big, nasty, messy things. More incentive to learn! Maybe I should just start and stick with MySQL, it seems to be the most popular.