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?

Reply

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...

Reply

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.

Reply

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.

Reply

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.

Reply

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.

 

 

 

Reply

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?

Reply

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

lonebyrd, you are right in asking that question

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.


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.

Reply