0

I'm building a new DB using MySQL to store lessons learned across a variety of projects. When we talk about this in the office, we refer to lessons by the Project Number and Lesson Number, i.e. PR12-81, where PR12 refers to the project and 81 refers to the specific lesson within that project. I want the primary key in my DB to have a hyphen in it as well.

When defining a composite key in SQL, I can make it reference the project and lesson but without the hyphen, i.e. PR1281. I've also considered creating a separate column of data type CHAR(1), putting a hyphen in every row and delcaring that the PK is made of 3 columns.

Is there another way that I can specify the primary key to be formatted in the preferred way?

Haggishunt56
  • 39
  • 1
  • 9
  • 2
    If the key is composite then leave out the hyphen and add it back when you write a view or query for the UI layer. It doesn't need to be stored. IMHO. – nicomp Mar 09 '20 at 15:04
  • This is a faq. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Mar 09 '20 at 21:23
  • Does this answer your question? [Proper database model for a user feedback system (an interesting case)](https://stackoverflow.com/questions/28364258/proper-database-model-for-a-user-feedback-system-an-interesting-case) – philipxy Mar 09 '20 at 21:25

1 Answers1

2

Let your table's primary key be a nonsensical auto-increment number with no "meaning" whatsoever. Then, within that table, define two columns: project_number and lesson_number. If the two need to be unique, define a UNIQUE index encompassing the two fields.

Don't(!) create database keys which embed information into them, even if the business does so. If the business needs to refer to strings like PR12, so be it ... create a column to store the appropriate value, or use a one-to-many table. Use indexes as needed to enforce uniqueness.

Notice(!) that I've now described four columns:

  1. The auto-increment based "actual" primary key, which contains no information.
  2. The project_number column, probably a foreign key to a projects table.
  3. Ditto the lesson_number. (With a UNIQUE composite index if needed.)
  4. The column (or table) which contains "the string that the business uses."

Over time, business practices do change. And someday you just might .. no, you will... ... encounter a "business-used string" that was incorrectly assigned by the human-beings who do such things! Your database design needs to gracefully handle this. The schema I've described is so-called third-normal form. Do a Google-search on "normal forms" if you haven't already.

Mike Robinson
  • 8,490
  • 5
  • 28
  • 41