第一个数据库结构帮助请
This is my first time making my own mysql database, and I was hoping for some pointers. I've looked through previous questions and found that it IS possible to search multiple tables at once... so that expanded my posibilities.
What I am trying to do, is have a searchable / filterable listing of Snowmobile clubs on a PHP page.
These clubs should be listable by state, county or searchable for by name / other contained info.
I'd also like them to be alphabetized in the results, despite the order of entry.
Currently my mind was in the place of, have a table for NY, PA etc
With Columns for County(varchar), Clubname(varchar), Street address (long text) , phone (varchar) email (varchar) website address (varchar)
Should I really be making multiple tables for each county, such as NY.ALBANY , NY.MADISON
Are the field formats I have chosen the sensible ones?
Should Address be broken into subcomponents... such as street1, street2, city, state, zip.
Eventually, I think I'd like a column "trailsopen" with a yes or no, and change the tr background to green or red based on input.
Hope this makes sense...
这是我第一次创建自己的mysql数据库，我希望得到一些指示。 我查看过以前的问题，发现可以一次搜索多个表格......这样就扩大了我的能力。 p>
我想要做的是，有一个可搜索的 /可过滤的雪地摩托俱乐部在PHP页面上的列表。 p>
这些俱乐部应按州名，县名或可搜索名称/其他包含的信息列出。 p>
目前我的想法代替了，有一张NY，PA等表格
使用县（varchar）列，Clubname（varchar），街道地址（长文本），电话（varchar）电子邮件（varchar）网站地址（varchar） p> \ n
我是否应该为每个县制作多个表格，例如NY.ALBANY，NY.MADISON p>
我选择的字段格式是否合理？ p>
地址是否应分为子组件...例如street1，street2，city，state，zip。 p>
最后，我想我想要一个专栏“ 步道 打开“是或否，并根据输入将tr背景更改为绿色或红色。 p>
希望这有意义...... p> div>
Here is how I would setup your db:
state
id (tinyint) //primary key auto incremented unsigned
short (varchar(2)) // stores NY, PA
long (varchar(20)) // Stores New York, Pennsylvania
county
id (int) //primary key auto incremented unsigned
state_id (tinyint) //points to state.id
name (varchar(50))
club_county
id (int) //primary key auto incremented unsigned
county_id (int) //points to county.id
club_id (int) //points to club.id
club
id (int) //primary key auto incremented unsigned
name (varchar(100))
address (varchar(100)
city (varchar(25))
zip (int)
etc...
From my perspective, it seems like 1 table will be enough for your needs. MySQL is so robust that there are many ways to do just about anything. I recommend downloading and using MySQL Workbench, which makes creating tables, changing tables, and writing queries easier and quicker than embedding them in a webpage.
Download MySQL Workbench -> http://dev.mysql.com/downloads/workbench/
You will also need to learn a lot about the MySQL queries. I think you can put all the info that you need in one table, and the trick is which query you use to display the information.
For example, assume you only have 1 table, with all states together. You can display just the snow mobile clubs from NY state with a query like this:
select * from my_table where state = "NY";
If you want to display the result alphabetic by Club Name, then you would use something like this:
select * from my_table where state = "NY" order by clubname;
There is A LOT of documentation online. So I would suggest doing quite a few hours of research and playing with MySQL Workbench.
The purpose of Stack Overflow is to answer more specific questions that have to do with specific code or queries. So once you have built a program, and get stumped on something, you can ask the specific question here. Good luck!
U can a create a single table with composite key constraint. Like.. I have 3 department in a company and each have multiple num of sub department.so I can create a database like this.. Dept_id || sub_dept_id || Name || Sal || Address || Phone ..where Dept_id and sub_dept_id will jointly represent the primary key and beholds its uniqueness.
But remember if ur database is going to be too large,then think before u doing this step,u might need need clustering or index for that scenario. While writing SQL query,its good practise to divide a main module in num of sub module. So u can break the Adress. As per your yes/no.... use integer feild and plan it in a way that if its YES,it'll store 1 else 0(zero)...
You shouldn't make individual tables for the individual counties. What you should do instead is create a table for states, a table for counties, and a table for addresses.
The result could look something like this:
state (id, code, name), county (id, stateID, name), club (id, countyID, name, streetAddress, etc...)
The process used to determine what to break up and when is called "database normalisation" - there's actually algorithms that do this for you. The wiki page on that is a good place to start: http://en.wikipedia.org/wiki/Database_normalization
One long text for the street address is fine, btw, as are varchars for the other fields.
- Should I really be making multiple tables for each county, such as NY.ALBANY , NY.MADISON
It depends, but in your described case an alternative might be to have one database table with all the snowmobile clubs, and one table for all the states/counties. In the clubs table you could have an id field as foreign key which links the entry to a specific state/county entry. To get all the info together you'd just have to do a JOIN-operation on the tables (please refer to mysql documentation).
- Are the field formats I have chosen the sensible ones?
They would work..
- Should Address be broken into subcomponents... such as street1, street2, city, state, zip?
Essentially the question here is if you need it broken down into subcomponents, either now or in the future? If it is broken down, you have the data separated which makes further processing (eg generation of serial letters, automated lookups..) potentially simpler, but that depends on your processing; if you don't need it separated why make life more complicated?
so many answers already.. agreed.