Jump to content


Photo

Best way to set up table(s) when multiple values


  • Please log in to reply
5 replies to this topic

#1 Mal1

Mal1

    Advanced Member

  • Members
  • PipPipPip
  • 64 posts

Posted 25 January 2013 - 07:57 AM

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.

#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,373 posts
  • LocationCheshire, UK

Posted 25 January 2013 - 08:17 AM

I gave an answer to a very similar question earlier

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

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 Mal1

Mal1

    Advanced Member

  • Members
  • PipPipPip
  • 64 posts

Posted 25 January 2013 - 08:49 AM

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.


#4 Mal1

Mal1

    Advanced Member

  • Members
  • PipPipPip
  • 64 posts

Posted 25 January 2013 - 09:16 AM

Should there be for the categories table:

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


or just:

id
category

#5 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,373 posts
  • LocationCheshire, UK

Posted 25 January 2013 - 02:58 PM

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

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#6 Christian F.

Christian F.

    Advanced Member

  • Staff Alumni
  • 3,106 posts
  • LocationNorway

Posted 26 January 2013 - 06:35 AM

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.
Keeping it simple.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com