Mr. Furxheir, R.M.W.
Group: admins
(1791 posts total)
(last post: November 24, 2007 - 15:07)
Citizen #20:
Martí- Paìr Furxhéir |
I will process differently in designing the new Talossan Database, in the fact
that it will be done public, transparently, open to everyone !
I will propose, one table at a time the way I want to organize things, so that those
that want to comment will be able to do so, and those that simply want to be kept
informed will be able to.
First, I will try to give to everyone a primer on databases. Feel free to ask as many
questions as you want, even those you think are stupid, for this is very complex stuff, and
I will try to simplify as much as possible.
Just try not to jump ahead.. I will try to present things in a rational and organized method.
In a way, this committee, which anyone can comment on, will be a shadow of the constitution committee,
since what happend in the constitution affects the database.
The only difference, is that I will make the decision myself, based on feedback, rather than putting
issues to a vote.
I WILL possible ask for a few votes on different alternatice, but often, I will only present
the surface of things, since it would be too long to explain concepts such as the Third Normal Form,
or optimization of Queries.
I will explain the surface of things, but it will not make experts of database design out of you, thought
it might encourage a few to read more on the subject !
Let's start with lesson one :
Relational Databases are composed of a series of Tables, which is simply a structured way of organizing content.
Each Table is composed of fixed Fields, also called columns, and certains Tuples, also called rows.
Relational means that tables may have relations between them, such as a Cosâ Seat linking a Citizen to a Cosâ.
For example, the Citizens table from the old database contains the following Fields :
-----------------------------
Name text
BirthDate text
Location text
Province int(11)
DateOfCitizenship text
TalossaName text
Occupation text
Party tinytext
Titles text
Biography longtext
Talography longtext
Other longtext
VoteExplain longtext
CosaSeatExplain longtext
TitleExplain longtext
Number int(11)
Active tinyint(4)
DateOfLosingCitizenship tinytext
ModificationAccessLevel tinyint(4)
Password tinytext
LastmodificationBy tinyint(4)
ProtectionFromModification tinyint(4)
Email text
Address text
AllowDataEntry tinyint(4)
FirstElection tinyint(4)
LastElection tinyint(4)
PrimaryPhoneNumber tinytext
SecondaryPhoneNumber tinytext
InSoSOffice tinyint(4)
SenateEligible tinyint(4)
RealName text
--------------------------------------------
First, is the field name ( ex: BirthDate ), then, the field type, with in parenthesis the size.
tinyint(4) means a 32 bits number, or a value from -2,147,483,648 to 2,147,483,647
Some of them have fixed values. For example, Active : 0 is Inactive, 1 is Active citizen, 2 is Dandelion.
Others are not "visible". RealName for example, is used for when a citizen, such as Myself, asks for his real name to be removed.
I still need a way to get the real name, to know who to ask when calling for example.
Some are for display reasons only, such as : FirstElection and LastElection, to black out the elections when the citizen couldn't vote.
These are the fields. There is exactly one Tuple ( or row ) per Citizen.
When a PHP page is run, it can query the database and retrieve values from several tables, using relations between them.
For example, in the CosaSeat table, one of the fields is the CitizenNumber field, which give the citizen number.
This is called a Foreign Key. A Foreign key is a field in one table, that references another table. Usually, the "primary key" of that table.
In the case of the Citizens table, the primary key is "Number".
As such, any table that refer to a citizen will have a field called usually CitizenNumber, and which will contain the number of the citizen.
Then, the PHP script can ask the database to replace the citizen number with the name of the Citizen, or in fact, display any number of fields, such as
Active, Titles, etc...
If you have ANY questions, please feel free to ask, I will try to make everything clear, so we can all be familiar with the Database and give ideas.
Trespassers will be shot.
Survivors will be short again. |
