5 replies to this topic

[Mal1]

Should be a fairly simple question.

I want to set up a fairly simple database with company information (name, email contact etc.) and the category or categories they serve. Companies in the table may be associated to one category or may be associated with 3 or 4. Should I make another table for categories (and if so how would keys be set?) or just have it in the one table?

Having a programmer link it all up and stuff, it's not my area, but would like to just have the tables set up and ready.

[Barand]

I gave an answer to a very similar question earlier

http://forums.phpfre...e/#entry1408132

[Mal1]

I gave an answer to a very similar question earlier

http://forums.phpfre...e/#entry1408132

I seen that thanks... so the categories table would I just have one row for id (what kind of key should I make id on this table and the id on the business table?) and one row for category?

My business table looks like this:

id smallint(6) auto_increment
business varchar(50)
contact varchar(50)
email varchar(100)
phone varchar(20)
address varchar(125)
city varchar(20)
postcode varchar(20)
description text

Also, the collation seems to default at "latin1_general_ci" is this normal/ok/matter?

Edited by Mal1, 25 January 2013 - 08:51 AM.

[Mal1]

Should there be for the categories table:

id
business id (linked with id on the business table)
category


or just:

id
category

[Barand]

You would have 3 tables

Business table (as now) : PK = id
Category : id, cat_name . (PK = id)
Businesscategory : business_id, cat_id (PK=business_id, cat_id)

The third table is the link table between the business and category tables

[Christian F.]

I recommend looking up on "Database normalization", starting with this video series:


It should help explain how to properly design a database, or at least get you started on it.

As for the collation/charset: Latin1 works, but UTF-8 is recommended as it allows you to store any character without jumping through hoops.