skip to Main Content

Context: I’ve been creating a database for a class assignment, the assignment doesn’t have detailed instructions, instead gives us a narrative in order to have us make our own assumptions and interpretations as to what to create for the database. Essentially a database that has employees and a factory. Factories are identified using their Full Address ,and have several other attributes.

I essentially have the entire ERD modeled and MYSQL code drafted based on my interpretation of the narrative, however I’m having zero clue on what to do with the "Full Address" identifier. Considering it says that its a "full address", my two main assumptions are either this:

I create a composite primary key with Street Number, Road, City, etc.

Or I make a varchar primary key that simply has the address as a string variable and nothing much.
This is what I have originally, but I’m having second thoughts.

CREATE table factory
( address VARCHAR(100),
  yearbuilt INT,
  PRIMARY KEY (address)
);

If I should make a composite primary key for this table, how would I code a composite primary key for a full address in MYSQL?, and how would I code this as a foreign key for other tables?

2

Answers


  1. How to make composite primary key and make it foreign key for other table: https://stackoverflow.com/a/10566463/22890637

    But that will make it harder for you in production. I agree with Adrian to make an auto-incrementing integer column. It would yield in better result and easier to manage data.

    If you thinking about making faster query by using composite primary key, it would be better to use multiple columns index.

    It’s your decision, you do what suits your needs best.

    Login or Signup to reply.
  2. If you use it in other tables as foreign key as well, i would suggest you use a separate column as primary key, because referencing composite keys as foreign keys can be a pain.

    I also would advice to let more room for address as 100 chars, when the data can be input online by people, and there are possibilities address is bigger than 100 chars. ofc. if you have only a closed source file that is all in, then it doesnt matter.

    in many databases you can use something like AUTO_INCREMENT for having an primary key filled easy.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search