第一数据库结构帮助请 [英] First Database Structure help Please

问题描述

这是我第一次创建自己的mysql数据库，我希望有一些指针。我浏览了先前的问题，发现可以一次搜索多个表...从而扩大了我的能力。

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.

我正在尝试做的事情，在PHP页面上有可搜索/可过滤的Snowmobile俱乐部列表。

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.

目前介意的地方是，有一张NY，PA等的桌子。

Currently my mind was in the place of, have a table for NY, PA etc

带有县（varchar），俱乐部名称（varchar），街道地址（长文本）的列，电话（varchar）电子邮件（varchar）网站地址（varchar）

With Columns for County(varchar), Clubname(varchar), Street address (long text) , phone (varchar) email (varchar) website address (varchar)

我真的应该为每个县制作多个表，例如NY.ALBANY，NY.MADISON

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?

应该将地址分解为子组件吗…… street1，street2，城市，sta

Should Address be broken into subcomponents... such as street1, street2, city, state, zip.

最终，我想我想用 yes或 no列 trailsopen，并根据输入将tr背景更改为绿色或红色。 。

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

推荐答案

我将如何设置您的数据库：

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

这篇关于第一数据库结构帮助请的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！