Tables setup help

psycorpse

I am setting up a new database. The database will hold company information. I am trying to make the tables for the company. In the process I am wanting to have 2 types for the company. It will be either Primary or Secondary. If it is secondary how would like it to the primary.

I have thought of a second table just for primarys but it is nessecary or should i create a field that links to the company id. Here is what I have so far for the company table:

id
name
type

the address and contacts have their own table.

any advice would be great.

rachel2004

Okay, so you're doing some db design this evening... ;-)

Explain to me the reasoning behind the Primary and Secondary table. Better yet, give them descriptive names so I can see where you are going with your design.

It's always a great idea to normalize your databases, but give your reasoning behind your strategy so I can tell you what type of problems may exists down the road.

Then I can help you out.

psycorpse

well here is what I am tring to accomplish.

Lets say I had a company that had multiple sites gets entered into the db. I would want all the other sites entered into the db too. So the main location would be the Primary and all the other sites would be a secondary. All the sites that are secondary would link to the primary. This is where I dont know if 2 tables would be better or just another field that links to the company id...

If i used one table I guess I would do something like:

company_id
company_name
type (primary or secondary)
company_parent_id (which would link to the company_id of the primary company.

rachel2004

In this case, I would creat a separate table for the website addresses. The reason being, you never know how many domains, subdomains, etc. a company will have. They could have:

www.abc.com
subdomain.abc.com
www.abc.com/anotherdirectory

and so on.

It will be too problematic for you to continue adding columns for each new website address if you only had one table, so you're on the right track of breaking things up.

So do you link them, right? Maybe do it like this:

COMPANY (your first table):
company_id
company_name

WEBSITES (website addresses):
company_id
website_url

You would then have a one-to-many relationship between tables COMPANY and WEBSITES with the company_id column being the linking portion.

If you were to do a query for all website of a company that had a company_id of 10, it would look like:

$sql = "SELECT c.company_name,
w.website_url
FROM COMPANY AS c
LEFT JOIN WEBSITES AS w
ON (c.company_id = w.company_id)
WHERE c.company_id = '10'";

psycorpse

Well i total understand about the website things but when i said sites i meant locations (sorry for the assumption that you knew what I was talking about). For instance:

Public Library (primary)
secondary branch (sec)
another branch (sec)
another branch (sec)

and there could be more that is just an example.

the secondary branch and the another branch should link up to the public library (primary)

rachel2004

In that case, it is safe to assume that each library can itself be a primary site. If we use NY City as an example, there would be:

New York Central Libary (primary site)
Manhattan Branch (secondary site)
Brooklyn Branch (maybe also considered a secondary or tertiary)

etc.

If the Brooklyn Branch had no relationship with the New York Central Libary location, then the Brooklyn Branch could be considered a primary site. We don't know at this time. We're just concerned with making sure the database can accomodate, right?

I hope I'm following you so far.

Here are the tables:

COMANY
company_id,
company_name

SITES
company_id,
site_name,
ranking

Notice I included a column called "ranking" where you could enter an integer to give it a preference in the hierarchy.

So, using libraries as an example, maybe for the company_name, you could have something like "New York Public Library System" with a company_id of "10", okay?

We might consider the Manhattan Branch as the primary location, so would give that location a ranking of "1". Brooklyn Branch might be considered a secondary site so we give it "2".

If we wanted to list the primary and secondary locations of this company, then, the query would look like:

$sql = "SELECT c.company_name,
s.site_name
FROM COMPANY AS c
LEFT JOIN SITES AS s
ON (c.company_id = s.company_id)
WHERE c.company_id = '10'
ORDER BY s.ranking";

Hope that all made sense.

psycorpse

Thank you.

-psycorpse