Specific Keywords To Create Table?

[lonebyrd 0]

I've made up a database before so I know the very basics of how to do one. I followed an outline of a script for a login/registration page once. But now that I'm a little further along in my site, I want to make up one specific for things I am doing. My question is, can you use any regular words in the tables of you database, or are they specific in MySQL? I mean, does MySQL only recognize certain words when making out your database.Example: What I am working on is needing a database for seperate 'stations' (as in T.V. for my game). In each station, there is a certain number of crew, sets and advertisements it is allowed due to it's size. Can I be specific like saying crew, sets, ads, in my database? And actually, now that I think about, how exactly do I go about that?

[seec77 0]

Well, actually, I think you'll have to use a few tables. But about your first question, unless I misunderstood you (because you were being a bit vague), then yes, almost every DBRMS allows for your own table/column names and whatever.And about the stations thing. I think you're going to have to create a few seperate tables, the first being a 'station' table, which must at least contain an ID column (a AUTO_INCREMENTed integer field). Next, you add a 'crew' table, that has another ID column, and another column to write the station ID to which it connects. I hope you get my point...

[Hercco 0]

So basically your question is that are there any limitations in choosing table and attribute names in MySQL?And the answer is that, apart from some special characters, you can have pretty much anything as table names. So for exaple "channel" and "crew" are completely OK as table names. However, some people including me, prefer to have some prefix for table names. That enebles you to use the same database for several applications without the risk of name collisions. Also attribute names can be anything.

[yordan 10]

apart from some special characters

I think there are also some reserved words in table names .Probably a table cannot be named "index", an index can probably not named "table", and so on. This field should more efficiently be explored using a good old manual. SQL is a "simple query langage" ; however, like all languages, it has a very precise syntax which should be learned with a teacher or with a manual.

[lonebyrd 0]

Thanks for the info. From the sounds of it, I'm going to need to a little more research into MySQL before I can actually do my database. I've only dabbled in it. But at least I know I can use almost any words I want. I suppose I could have tried to look this question up by googling it or something, but I come up with the weirdest things that way. I have the hardest times finding good resource material in one spot on subjects, and I'm getting tired of going from site to site. But I guess, since I can't afford to go to school, this is how I'll have to learn.

[Houdini 0]

For the benefit of Lonebyrd and others here is a list of MySQL reserved words. You might want to copy and paste these words and have them handy. This is not my own list but one from a website. When answering some MySQL problems on the PHP Builder site I have found that some of the problems are caused by nameing tables or columns with one of the reserved words. Although it is possible to actually create such a table or column it is when you try to access it is when the troubles crop up. This list will help to avoid that. Keep in mind that the case of these words does not matter. In other words SAME is the same to MySQL as well as Same or sAME saME samE saMe (does that make sense)?
Before getting to far into planning, there is a number of words that you cannot use for your table and column names. MySQL uses some specific words for command processing, so using them in your own coding may confuse things a bit.

ADD ALL ALTER ANALYZE AND AS ASC ASENSITIVE AUTO_INCREMENT BDB BEFORE BERKELEYDB BETWEEN BIGINT BINARY BLOB BOTH BY
CALL CASCADE CASE CHANGE CHAR CHARACTER CHECK COLLATE COLUMN
COLUMNS CONDITION CONNECTION CONSTRAINT CONTINUE CREATE
CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURSOR DATABASE
DATABASES DAY_HOUR DAY_MICROSECOND DAY_MINUTE DAY_SECOND DEC
DECIMAL DECLARE DEFAULT DELAYED DELETE DESC DESCRIBE DETERMINISTIC DISTINCT
DISTINCTROW DIV DOUBLE DROP ELSE ELSEIF ENCLOSED ESCAPED EXISTS
EXIT EXPLAIN FALSE FETCH FIELDS FLOAT FOR FORCE FOREIGN FOUND FRAC_SECOND FROM
FULLTEXT GRANT GROUP HAVING HIGH_PRIORITY HOUR_MICROSECOND
HOUR_MINUTE HOUR_SECOND IF IGNORE IN INDEX INFILE INNER INNODB
INOUT INSENSITIVE INSERT INT INTEGER INTERVAL INTO IO_THREAD IS
ITERATE JOIN KEY KEYS KILL LEADING LEAVE LEFT LIKE LIMIT LINES LOAD
LOCALTIME LOCALTIMESTAMP LOCK LONG LONGBLOB LONGTEXT
LOOP LOW_PRIORITY MASTER_SERVER_ID MATCH MEDIUMBLOB MEDIUMINT
MEDIUMTEXT MIDDLEINT MINUTE_MICROSECOND MINUTE_SECOND MOD NATURAL
NOT NO_WRITE_TO_BINLOG NULL NUMERIC ON OPTIMIZE OPTION OPTIONALLY OR
ORDER OUT OUTER OUTFILE PRECISION PRIMARY PRIVILEGES PROCEDURE PURGE
READ REAL REFERENCES REGEXP RENAME REPEAT REPLACE REQUIRE RESTRICT
RETURN REVOKE RIGHT RLIKE SECOND_MICROSECOND SELECT SENSITIVE SEPARATOR SET
SHOW SMALLINT SOME SONAME SPATIAL SPECIFIC SQL SQLEXCEPTION SQLSTATE
SQLWARNING SQL_BIG_RESULT SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT SQL_TSI_DAY SQL_TSI_FRAC_SECOND SQL_TSI_HOUR SQL_TSI_MINUTE SQL_TSI_MONTH
SQL_TSI_QUARTER SQL_TSI_SECOND SQL_TSI_WEEK SQL_TSI_YEAR SSL STARTING
STRAIGHT_JOIN STRIPED TABLE TABLES TERMINATED THEN TIMESTAMPADD TIMESTAMPDIFF TINYBLOB TINYINT TINYTEXT TO TRAILING TRUE UNDO UNION UNIQUE UNLOCK
UNSIGNED UPDATE USAGE USE USER_RESOURCES USING UTC_DATE UTC_TIME UTC_TIMESTAMP
VALUES VARBINARY VARCHAR VARCHARACTER VARYING WHEN WHERE WHILE WITH
WRITE XOR YEAR_MONTH ZEROFILL

[lonebyrd 0]

From a quick scan, it doesn't look like I'd use any of those anyway. But that is a very handy reference indeed. I thought there must have been some words that were specific to MySQL. I've seen where words have specific meaning in other stuff I've done, although memory fails me as to what it was. I will have to copy/past this list somewhere for future reference, as I may need it someday.As to that list, aren't some of those just attributes? I remember in PHPmyAdmin that the second column I believe, is where you find things like AUTO_INCREMENT. What purpose would be served using that as a name for a database/table?

[SP Rao 0]

From a quick scan, it doesn't look like I'd use any of those anyway. But that is a very handy reference indeed. I thought there must have been some words that were specific to MySQL. I've seen where words have specific meaning in other stuff I've done, although memory fails me as to what it was. I will have to copy/past this list somewhere for future reference, as I may need it someday.
As to that list, aren't some of those just attributes? I remember in PHPmyAdmin that the second column I believe, is where you find things like AUTO_INCREMENT. What purpose would be served using that as a name for a database/table?

Honestly the list provided by Houdini was extensive and very handy indeed.

lonebyrd, you are right in asking that question

What purpose would be served using that as a name for a database/table?

to some extent... In your case you may not need to use names like AUTO_INCREMENT. May be there are times when you might want similar table names. But, what Houdini was pointing out was, the list of reserved words and in buit function names of MySQL, which can not be used for user objects.

Well, by the looks of it, you appear to be new to the very database concepts itself rather than MySQL alone. Going through RDBMS fundamentals and little SQL fundamentals would be helpful.

I'll just give you very brief details of database fundamentals. Now, you have created a database, alright. Vewry well, you are through with your first step. Now for your design, you need to identify data items which you want to be stored in your database. Once you identify all the details, you should start categerising them by relashonship. Now, you will have sub groups of details after you finish your categarization.

Now each such subgroup can be implimented in a table. (Table is nothing but, collection of data. For imagination you can think of it as a spreadsheet). Each element within the subgroup will be a "column" of the table.

Let us take your necessity.

Example: What I am working on is needing a database for seperate 'stations' (as in T.V. for my game). In each station, there is a certain number of crew, sets and advertisements it is allowed due to it's size. Can I be specific like saying crew, sets, ads, in my database? And actually, now that I think about, how exactly do I go about that?

Let us identify the details you will need in database. First let's just list them all. You have many separate stations. The station might have a name, let's a description and any other detail you want associated with it. Next, each station will have attributes like crew, aets, ads etc...
Now, as you can see, Station has some details related to it. Like, it's name, a description etc. Now all these things can be clubbed and put in a table say Station. The table will have columns like Station ID, Station Name, Station decription. (Just an example. I'm not too sure of what you want ) Once you create this table, you can just imagine it as spreadsheet. You know the columns. You can just keep on adding rows.
Now, there should be a "Primary Key" for your master table. Let it be Station ID. This will be unique in your table. No two rows will have same Station ID.

Now, every station will have attributes like crew, aets, ads etc. Now one station can have multiple crew, aets or ads. So you can maintai these things in different tables. I don't know your specific need. If you have further details associated with Crew , aet or ads, then you may have to create a different table for each of them. The primary key of you table Station (i.e. Station ID) should be present in all these tables as primary key. In other words, the column Station ID will be present in all these child tables.

Hope you are getting some idea now. I hope this helps you a bit. Anything more is needed just give exact details of the problem. Will be too gland to lend a hand to solve it.
Edited August 30, 2006 by SP Rao (see edit history)