Setting up a Lookup Table

[SaranacLake]

Hello.  I have some questions related to setting up a Lookup Table.

 

In the past, I set things up like this...

	ARTICLE
	- id
	- title
	- category (FK)
	- body
	

 

	CATEGORY (lookup table)
	- id (UK)
	- slug (PK)
	- name (UK)
	

 

	article.category ->0------||- category.slug
	

 

 

Here is the logic used on the above tables...

a.) Every table in my database has an "ID" that is an auto-increment, and usually the "PK".

b.) For my lookup table, I made "slug" the "PK" because I wanted an *English word* (e.g. "database-development") showing up in the main table.

c.) As such, I made the "ID" a "UK" to ensure it is unique.

d.) "Name" is just a pretty version of "slug" in case I need to display it or something like that (e.g. "Database Development").

 

 

Questions:

1.) Is it a mortal sin to NOT have the "PK" as the first column?

2.) Is it a mortal sin to have the auto-increment "ID" be a "UK"?

3.) Is it a mortal sin to have the "slug" be the "PK"?  (I could drop "ID", but I like have a number for each row as it makes it easier to visually find a given record.)

4.) If the "slug" is the "PK", can it have spaces in it (e.g. "database development") or must I have the hyphen?

 

 

 

 

Edited August 9, 2020 by SaranacLake

[requinix]

1 hour ago, SaranacLake said:

b.) For my lookup table, I made "slug" the "PK" because I wanted an *English word* (e.g. "database-development") showing up in the main table.

Don't do that. For one, it takes much more space to store whole strings like that. For two, there's a chance the slug may need to change in the future (like expanding the section to "database-design-and-development") and that would mean having to update all the tables using it.

Use the number. That's the whole point of why it exists. Make it the PK with auto-increment and make the slug unique.

 

1 hour ago, SaranacLake said:

d.) "Name" is just a pretty version of "slug" in case I need to display it or something like that (e.g. "Database Development").

There's no point in making this unique: (1) the uniqueness doesn't actually matter, and (2) the slug is basically the same as the name and it's already going to be unique.

 

1 hour ago, SaranacLake said:

1.) Is it a mortal sin to NOT have the "PK" as the first column?

No, but if it isn't in the first couple columns then I would be suspicious. I would also be suspicious if there was a numeric "ID" column and it was not the PK.

 

1 hour ago, SaranacLake said:

2.) Is it a mortal sin to have the auto-increment "ID" be a "UK"?

(And while not being the PK.) Just about.

 

1 hour ago, SaranacLake said:

3.) Is it a mortal sin to have the "slug" be the "PK"?  (I could drop "ID", but I like have a number for each row as it makes it easier to visually find a given record.)

Just about. At least when there are better alternatives.

 

1 hour ago, SaranacLake said:

4.) If the "slug" is the "PK", can it have spaces in it (e.g. "database development") or must I have the hyphen?

Database doesn't care.

[SaranacLake]

@requinix

I sorta figured you'd say what you said.  😉

Okay, so let's say I have a lookup table that will NOT be changing any time soon (e.g. U.S. States).

For brevity, could my lookup table just have ONE field called "slug" (or maybe "name") that lists the state abbreviations?  (i.e. AK, AL, AR, AZ,...)

The logic being for simple lookups like that, it seems like a waste to have columns like: ID, slug/abbreviation, full-name, etc

(In that case, the ONE column would be the PK!)

 

[requinix]

3 hours ago, SaranacLake said:

Okay, so let's say I have a lookup table that will NOT be changing any time soon (e.g. U.S. States).

*puerto rican sad face*

 

3 hours ago, SaranacLake said:

For brevity, could my lookup table just have ONE field called "slug" (or maybe "name") that lists the state abbreviations?  (i.e. AK, AL, AR, AZ,...)

"Abbreviation" or "Abbr". Please.

 

Quote

The logic being for simple lookups like that, it seems like a waste to have columns like: ID, slug/abbreviation, full-name, etc

How is it wasteful? In this particular example, the state abbreviation is short and so I wouldn't mind it being the primary key (in which case don't have an ID column at all), But the abbreviation and full name are both "lookup"-able values.

BUT I would not make a table that has only US states. I would pretty much always have to think about Canada, or maybe Mexico, or Australia, or some of the other countries that have states/provinces. In which case the abbreviation is no longer unique by itself - you'd need a country FK, and while country + abbreviation would be unique, I would not have the two act as the primary key (mainly due to the awkwardness of a composite PK).

[SaranacLake]

9 hours ago, requinix said:

*puerto rican sad face*

I knew you were going to say that!  *LOL*

(Until they figure out where to put that 51st star, it'll never happen!)  😉

 

 

Quote

"Abbreviation" or "Abbr". Please.

Okay.

 

Quote

How is it wasteful? In this particular example, the state abbreviation is short and so I wouldn't mind it being the primary key (in which case don't have an ID column at all),

Okay, good.

 

 

Quote

But the abbreviation and full name are both "lookup"-able values.

Sure.

 

 

Quote

BUT I would not make a table that has only US states. I would pretty much always have to think about Canada, or maybe Mexico, or Australia, or some of the other countries that have states/provinces. In which case the abbreviation is no longer unique by itself - you'd need a country FK, and while country + abbreviation would be unique, I would not have the two act as the primary key (mainly due to the awkwardness of a composite PK).

True, but since for now I am only accepting customers in the U.S. that won't apply.

(It might be too hard to allow Candian customers, but I'm not even sur ethat my payment processor allows payments from overseas, but I see your point.)

 

 

Lastly, so let's say I have another lookup table where I could use the lookup value itself as the PK - because it isn't too long and wouldn't likely change.

As mentioned in my OP, I like have an auto-increment on all tables because it is easier to find records manually looking in the table or maybe if you exported on a report by looking for a numeric ID. 

Would you rather scan a table looking for this...

	SKU (PK)
	MM1100
	MMI100 <== That's an "I' (eye)
	MN1100
	MMIIOO <== Some Oh's there
	

 

Or...

	ID (??)		SKU	(PK)
	1		MM110
	2		MMI100
	3		MN1100
	4		MMIIOO
	

 

Even if the ID's aren't sequential, it is just way easier to work with an ID as a human! 

(Let MySQL have it's SKU as the PK, but for me I'd prefer an ID...)

 

So, isn't there a way that I can have my "ID" but use something else as the PK and still not commit a mortal sin??

 

 

Edited August 9, 2020 by SaranacLake

[requinix]

2 hours ago, SaranacLake said:

I knew you were going to say that!  *LOL*

(Until they figure out where to put that 51st star, it'll never happen!)  😉

https://en.wikipedia.org/wiki/History_of_the_flags_of_the_United_States#Possible_future_designs

 

2 hours ago, SaranacLake said:

Would you rather scan a table looking for this...

I would rather the table had a simple integer as the PK.

 

2 hours ago, SaranacLake said:

So, isn't there a way that I can have my "ID" but use something else as the PK and still not commit a mortal sin??

Asked and answered.

[SaranacLake]

23 minutes ago, requinix said:

https://en.wikipedia.org/wiki/History_of_the_flags_of_the_United_States#Possible_future_designs

Who woulda thunk?! 

[itmagician]

Hi -

I am looking to add to a web form a pop-up window that has selected data (rows) for selection and then on submit of lookup populates the calling web form field.

Any ideas of examples I can look at to use as a road map?

Thank you!

[Barand]

1. Don't hijack other people's topics
2. Try Google