-2

My general goal is to have a properly designed basic inventory system. However, I am having trouble finalizing my DEVICE table.

DEVICE is used for storing each type of device within my inventory, not a specific device instance. Another table, INVENTORY, will be used to store each actual device. The attributes within my original DEVICE is as follows:

DEVICE(
  model,
  category,
  brand
)

An example:

a_device(
  model: iPhone X
  category: phone
  brand: apple
)

Goal is to decrease any chance of an anomaly, so I added two new tables and simply had DEVICE reference them.

Model is specific to a single row within DEVICE, since DEVICE is storing a single type of item, not a list of my inventory. Category and Brand however, will have repeat values among the rows since there is more than one type of iPhone available and Apple produces more than just phones.

DEVICE(
  model
  category_name
  brand_name
)

CATEGORY(
  category_name
)

BRAND(
  brand_name
)

Would this be the correct layout? To have DEVICES.category_name and DEVICES.brand_name be foreign keys that reference the CATEGORY and BRAND tables?

philipxy
  • 14,867
  • 6
  • 39
  • 83
JudosOk
  • 1
  • 2
  • This is not clear. What do you mean by "(DB) normalization" & what does this have to do with it & how are you doing it & how are you unsure? What do you mean by "determines"? Normalization to higher NFs uses "functionally determines" but it has a specific meaning & applies within 1 table. Normalization does not introduce ids (supposedly identifying values better than they identify themselves). Weakness is irrelevant. Rearranging for subtype tables is not normalization. What is your goal, given what? PS "the way I see it" is a prose smell typically meaning, I don't see it but guess that maybe. – philipxy Aug 25 '23 at 20:20
  • First, you have to define relationships to create a relational database. For example, a device type belongs in one and only one category. Wikipedia has a helpful article, [database normalization](https://en.wikipedia.org/wiki/Database_normalization). – Gilbert Le Blanc Aug 25 '23 at 22:15
  • Your edit hasn't helped much. What published DB design textbook/reference are you following? What method? What are its steps? Where is the 1st place you are stuck? Why/how are you stuck? You essentially ask us to (re)write a textbook for an unidentified method & bespoke tutorial with no details on what you misunderstand or do or don't understand. [ask] [Help] Basic questions are faqs. [research effort](https://meta.stackoverflow.com/q/261592/3404097) PS Please avoid social & meta commentary in posts. – philipxy Aug 26 '23 at 00:40

0 Answers0