1

I'm designing my first real database and I'm weighing the pros and cons of how to enforce uniformity in my data. My first option is to create a custom domain and program my UI to only allow certain options. My second option is to create an "attribute reference table" that would store allowed values. My UI could query that table for allowed values and display them in a drop down box. Or I could do both

My thoughts on the first is that it would be more difficult to maintain the UI. If the domain were to change, then I would need to alter the UI (unless there is a way to query the domain for values?). I don't know whether there is, but my thought is this would process and return results faster than having to query a second table.

An attribute table seems beneficial in that I can design the UI to adapt on the fly, but less optimal in the sense that the data values are not controlled by the DBMS and it requires an additional query.

Doing both would add all of the complexity, increase processing time, and increase UI maintenance.

Jonathon Anderson
  • 137
  • 5

1 Answers1

-1

Though specific problems are missing in the question provided, I would try to answer in a more generic way of how to design DB based on my personal experience.

  • First step would be identifying entities and their relationships.
  • Try normalizing data based on normalization rules so that your table structure doesn't allow redundant data & data dependency make sense. You may take a cue about this from Data Normalization @ Wikipedia
  • Now talking about specifics its always a choice or trade off between keeping things dynamic and tying them in structure. Since the exact problem is not mentioned i'll take an example of my choice. Lets say you want to input a country name and based on it some more attributes and it could be dynamic. But since there could be FINITE number of countries it would make sense by having a master table of countries whose data doesn't change and you may use it as reference in your real data. But if you were to have a list of N attributes with M types (where M is also infinite like N, which again is a rare possibility) you would have to go with the former option provided in your question.
  • Just a hint, key here would be to identify the types of attributes / entities.
user3630961
  • 1
  • 1
  • I think your answer is too broad. You are listing good practices when designing a database, which is not wrong in itself, but you are not addressing the main question: should the OP create a reference table or keep a custom domain in the UI? Maybe you can improve your answer by being more explicit about what would be your choice? – logc Jun 25 '14 at 10:16