Greenguy's Board


Go Back   Greenguy's Board > General Business Knowledge
Register FAQ Calendar Today's Posts

Reply
 
Thread Tools Search this Thread Rate Thread Display Modes
Old 2018-01-22, 09:34 PM   #1
Toby
Lonewolf Internet Sales
 
Toby's Avatar
 
Join Date: Mar 2005
Location: Houston
Posts: 4,826
Send a message via ICQ to Toby
Database structure advice needed

I hope I can explain the situation well enough that those of you that are database peeps can offer recommendations.

Situation: A large list of primary names, each with up to three aliases, along with other info.

Desired output: An alphabetic list by name that shows the info including the aliases, plus within the list in the appropriate places alphabetically, the alias names with the associated primary name.

So... would it be best to put it all in one table with fields for name, alias1, alias2, alias3, etc.

OR

Would two tables, one with the primary names and info, then a second table with aliases and the associated primary name?

I'm using PHP and a Mysql database.

Thx
Toby is offline   Reply With Quote
Old 2018-01-23, 12:19 PM   #2
pc
Shift Out / X-On
 
pc's Avatar
 
Join Date: Jul 2007
Location: unknown
Posts: 2,298
Send a message via ICQ to pc
I would definitely use one table and sort the required outpud by just changing the variables by AND and OR LIKE in mysql query.

But if you want to use same db and call by aliases in other places other table just for aliases sound like best bet. You just need to join tables by id for same person. I guess id would numerical. Then its easy to join tables by id and get the values from other table.

Option number 2 sounds better but its more code writing. But then your db would be very flexible for other projects.
pc is offline   Reply With Quote
Old 2018-01-23, 02:48 PM   #3
Toby
Lonewolf Internet Sales
 
Toby's Avatar
 
Join Date: Mar 2005
Location: Houston
Posts: 4,826
Send a message via ICQ to Toby
Thanks, solution 1 sounds like the right fit for this situation.
Toby is offline   Reply With Quote
Old 2018-01-23, 03:58 PM   #4
pc
Shift Out / X-On
 
pc's Avatar
 
Join Date: Jul 2007
Location: unknown
Posts: 2,298
Send a message via ICQ to pc
Quote:
Originally Posted by Toby View Post
Thanks, solution 1 sounds like the right fit for this situation.

For strictly coding querries/solutions I usually browse StackOverflow. It's a plethora of knowledge.
pc is offline   Reply With Quote
Old 2018-01-23, 09:50 PM   #5
Toby
Lonewolf Internet Sales
 
Toby's Avatar
 
Join Date: Mar 2005
Location: Houston
Posts: 4,826
Send a message via ICQ to Toby
Quote:
Originally Posted by pc View Post
For strictly coding querries/solutions I usually browse StackOverflow. It's a plethora of knowledge.
I've run across a few things from StackOverflow when searching Google. Thanks for the tip.
Toby is offline   Reply With Quote
Old 2018-01-30, 12:48 PM   #6
sarettah
Asleep at the switch? I wasn't asleep, I was drunk
 
Join Date: Apr 2005
Posts: 214
2 tables.

1 with the primary name and an associated key.

2nd table with the primary key and a field for alias.

That way you are not limited on number of aliases and do not have to change structure when you decide you need 4 instead of 5.

Primary table:

Id - auto-increment primary key
Primary_name Varchar how ever long you need it to be

Alias table:

id - auto-increment key
primary_id - points at key in primary table
alias_name varchar however long you need

Example:

Primary table:

1 Primary Name 1
2 Primary Name 2
3 Primary Name 3

Alias Table:

1 1 Alias for Primary 1 1
2 1 Alias for Primary 1 2
3 1 Alias for Primary 1 3
4 2 Alias for Primary 2 1
5 2 Alias for Primary 2 2

Pull primary with their aliases

Select a.primary, b.alias
from primary_table a left outer join alias_table b on a.id=b.primary_id
order by primary, alias

Hope that makes sense
sarettah is offline   Reply With Quote
Old 2018-01-30, 10:43 PM   #7
Toby
Lonewolf Internet Sales
 
Toby's Avatar
 
Join Date: Mar 2005
Location: Houston
Posts: 4,826
Send a message via ICQ to Toby
Thanks Sarettah, I was part way there already. You've confirmed I was headed in the right direction.
Toby is offline   Reply With Quote
Old 2018-01-31, 11:38 AM   #8
sarettah
Asleep at the switch? I wasn't asleep, I was drunk
 
Join Date: Apr 2005
Posts: 214
Quote:
Originally Posted by Toby View Post
Thanks Sarettah, I was part way there already. You've confirmed I was headed in the right direction.
You are quite welcome
sarettah is offline   Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 07:30 PM.


Mark Read
Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
© Greenguy Marketing Inc