Results 1 to 12 of 12

Thread: Database Design

  1. #1
    Join Date
    May 2016
    Posts
    6

    Unanswered: Database Design

    ok, ive got a final project to do for database design and we're just basically creating our own databases.

    my database is gonna be based on how pizza factories set up their teams and theres an additional entity to keep track of dough garbage dumps per line, idk if what i had is the best way, i tried breaking things up into bridge tables but i kept thinking that the bridge tables were not needed. i need help on my design, i need at least 4 entities for this project.

    im currently tryna see if what i have for dumps is the best way and right now im tryna figure out how to add a team entity into the line entity, im gonna probably need an employee entity which represents each individual team member.

    let me know if i need to elaborate a little more on somethings.

    what i have so far: http://i.imgur.com/RCfTrJV.png

    i thought dump and line should have a bridge table because LINE will have MANY DUMP and DUMP will have MANY LINE but i mightve thought wrong.. idk if its a many to many situation

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well it depends on what your scenario is. bear in mind hat as part of this assessment the narrative of what you have included in (and just as importantly excluded from) the design (and the way that you reached the final design is important as the actual design. equally you can identify other areas that should / could be included.

    so you need 4 tables
    I'#d make life easier

    teams
    people
    products
    ?????
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you give a detailed description of the system you want to model?

    I don't own a pizza factory. I don't know what you mean by "team", "line" and "dough garbage dump". I do have an idea what each is, but I can't be sure.
    So define the "players", the entities, in your system, and describe how they interact. Like:

    A pizza factory has 1 or more lines where they process dough into pizza's. Each line can generate a number of different pizza types. Not every line is suited to make all kinds of pizza types. Every hour the containers that are located at each line and that contain the wasted dough are emptied. Each time the line number, the time and the weight of the garbage dough are recorded. .....
    ...
    The management wants to see a report
    - on what line the most garbage is generated: per type, per day, per week end per hour, ...
    - the top three pizza types that generate the most garbage dough in the last week.
    - per day, per pizza type: how many lines produced that pizza type + number of hours.
    - ...


    Tell us the story of what you want to model. By doing so, things will get clear for you too an you will find out how to model things. It is also necessary if you want help from the forum.

    Please do keep in mind that we do not make anybody's homework, but we will help you when we get proof that you have put in effort yourself.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    May 2016
    Posts
    6
    Quote Originally Posted by Wim View Post
    Can you give a detailed description of the system you want to model?

    I don't own a pizza factory. I don't know what you mean by "team", "line" and "dough garbage dump". I do have an idea what each is, but I can't be sure.
    So define the "players", the entities, in your system, and describe how they interact. Like:

    A pizza factory has 1 or more lines where they process dough into pizza's. Each line can generate a number of different pizza types. Not every line is suited to make all kinds of pizza types. Every hour the containers that are located at each line and that contain the wasted dough are emptied. Each time the line number, the time and the weight of the garbage dough are recorded. .....
    ...
    The management wants to see a report
    - on what line the most garbage is generated: per type, per day, per week end per hour, ...
    - the top three pizza types that generate the most garbage dough in the last week.
    - per day, per pizza type: how many lines produced that pizza type + number of hours.
    - ...


    Tell us the story of what you want to model. By doing so, things will get clear for you too an you will find out how to model things. It is also necessary if you want help from the forum.

    Please do keep in mind that we do not make anybody's homework, but we will help you when we get proof that you have put in effort yourself.
    Ok so basically, LINE basically represents something like the machines that produces the product and for every LINE it needs a team to operate it, that team will consist of 5-10 people. Also, every line eventually will have garbage to dump out. TYPE is just a table to keep track of the type of the line, whether it's a cold line, hot line, taco line or whatever. So basically, if I look at the dump table, I'd be able to find when it was dumped and what line dumped, then if I go to line, I'd find what the type of line is and who's all working on that line(the team)

  5. #5
    Join Date
    May 2016
    Posts
    6
    anyone? anything is appreciated.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Its your assignment...
    Its your work
    What have you got..
    What ideas of entities do you have.
    Is this your scenario or the institution you are studying at's
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2016
    Posts
    6
    Quote Originally Posted by healdem View Post
    Its your assignment...
    Its your work
    What have you got..
    What ideas of entities do you have.
    Is this your scenario or the institution you are studying at's
    Everything I've got is on here and the ideas are here, let me know if you want any elaborations and it's my scenario.

  8. #8
    Join Date
    May 2016
    Posts
    6
    another update on what im thinking about doing, but im just kind of winging it.

    http://img.prntscr.com/img?url=http:...om/uySLeCi.png

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    First, a little pedantic aside and request for more info ..

    Your design does not look right; you need to get a good book on basic data modeling. There is no such thing as a "type_ID" in RDBMS. That would violate the law of identity from formal logic. But we want to have is a "<something in particular>_type"instead. In RDBMS, "type" and "ID" are what ISO 11179 calls attribute properties. Think of them as kind of like adjectives on a noun; they have to have that noun (attribute).

    I have another rule of thumb that if an attribute has a small, static set of values (for example, "blood_type"), then put it in a check constraint. If the domain of the attribute is dynamic were very large, then put it in its own table and use a reference to it. This is partly a performance consideration, but also a design issue.

    I also do not understand what a "dump_id" is. I would think the "dump_timestamp" and "line_id" would be a natural key.

    I also have never heard the term "bridge table" in all the years I served on ANSI X3H2. Things like "link table" etc. were borrowed from the old network databases. In RDBMS a table is an entity, or a relationship, or an auxiliary table. This is where we get E-R diagrams and that whole method of modeling. (An auxiliary table is usually used for lookups, but it is basically anything that is not an entity or relationship set).

    You do not need a "employee" table; you need "personnel", which is a set abstraction and not an attempt to model paper form in SQL. Think sets and abstraction, not paper forms and punchcards.

    Just from the words and having no other specs I would have thought that a line would produce dumps at various times in the process. But that a dump could not occur on more than one line. But there is no way for me to tell so I am just guessing. It sounds like you will need a relationship table that references a team, and the team that references personnel. Then there will be a relationship between the line and the team. But we need to know if these relationships are 1:M, M:N or even 1:1.

  10. #10
    Join Date
    May 2016
    Posts
    6
    Quote Originally Posted by Celko View Post
    Your design does not look right; you need to get a good book on basic data modeling. There is no such thing as a "type_ID" in RDBMS. That would violate the law of identity from formal logic. But we want to have is a "<something in particular>_type"instead. In RDBMS, "type" and "ID" are what ISO 11179 calls attribute properties. Think of them as kind of like adjectives on a noun; they have to have that noun (attribute).

    I have another rule of thumb that if an attribute has a small, static set of values (for example, "blood_type"), then put it in a check constraint. If the domain of the attribute is dynamic were very large, then put it in its own table and use a reference to it. This is partly a performance consideration, but also a design issue.

    I also do not understand what a "dump_id" is. I would think the "dump_timestamp" and "line_id" would be a natural key.

    I also have never heard the term "bridge table" in all the years I served on ANSI X3H2. Things like "link table" etc. were borrowed from the old network databases. In RDBMS a table is an entity, or a relationship, or an auxiliary table. This is where we get E-R diagrams and that whole method of modeling. (An auxiliary table is usually used for lookups, but it is basically anything that is not an entity or relationship set).

    You do not need a "employee" table; you need "personnel", which is a set abstraction and not an attempt to model paper form in SQL. Think sets and abstraction, not paper forms and punchcards.

    Just from the words and having no other specs I would have thought that a line would produce dumps at various times in the process. But that a dump could not occur on more than one line. But there is no way for me to tell so I am just guessing. It sounds like you will need a relationship table that references a team, and the team that references personnel. Then there will be a relationship between the line and the team. But we need to know if these relationships are 1:M, M:N or even 1:1.
    the class is just a basic intro to database development class so theres not much there is.

    a pizza crust factory has many lines running creating their own individual pizza crust and sometimes pizza crusts arent created to meet standards so theyre thrown out(DUMPED). every line consists of many team members to run it. thats basically all what im tryna "model"

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is no reason to use an autogenerated column as a PK if there is another pre existing column or mix of columns which would do the job. That is unless the prop;soed PK is overly long, possibly might change or toehr performance reasons. a PK has to be able to uniquely identify a <whatever> so Celko's advice on not using a DUMP_ID makes sense as the combination of line and date time uniquely identifies a 'dump event'. in thsi case using a combination of line and datetiem of event makes sense as by doing so defacto you have elimnated a risk that the same piece of data may be captured more than once, whch would be a risk if you used an autogenerated column. of course it doesn't stop a data capture error (ie entering the wrong time).

    given that this is a made up scenario for the express purpose of an assigment and homework, but..
    can an employee be moved about between teams
    can a team work on different production lines
    can a production line produce different types of pizza crust
    ..read up on association / intersection tables

    a team should comprise may employees (or personnel in Celko speak), so I'd expect a team entity in its own right
    but its an artifical scenario part of the process is you creatign the scenario, creating a design that meets the scenario and (probably) pointing out where its weak / falls down, why you've limited it to the design you have, where you'd re work it if required and so on. ultimately its your work. posting abstract illogical scenarios here will tend to get response which are more 'real world' focussed. IE in order to have a product you need ingredients, and probably a recipie for a product.

    you will almost certainly get reminded to be consistent in naming, use CamelCase or underscores (and lower case for column names)
    use logical names for entities (ie dump is confusing waste or, say, wastage isn't)
    type as a table name is near meaningless, whereas product_type less so

    part of the art or skill of table./column design is giving names to entities so that not jsut you as the developer but others following on can understand the design with having to ask questions such as whats does dump mean?)
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'm sorry for not responding earlier. I was expecting that your problem would have been solved by now. Perhaps you have only a rough idea of what you want to design and that keeps people from helping you.

    Let's focus on a "line" and the "type of products" (or just "products" for short) that line can handle.
    Our factory has just started and has only two lines operational: A-line and B-line.
    Our factory can make different food products: pizza del mare, frozen pizza del mare, pizza margherita, frozen pizza margherita and taco's. Only A-line has the needed apparatus to make frozen pizza's.
    We have identified two entities: "Lines" and "Products". Because one line can make more than one product and a product can potentially be made on more lines, we need an extra entity to model those relations. Let's call that extra entity "Line product".

    The available lines:
    Line
    1. A-Line
    2. B-Line

    The products our factory can make:
    Product
    1. pizza del mare
    2. frozen pizza del mare
    3. pizza margherita
    4. frozen pizza margherita
    5. taco

    The products that each line can make:
    Line_Product
    1. A-Line pizza del mare
    2. A-Line frozen pizza del mare
    3. A-Line pizza margherita
    4. A-Line frozen pizza margherita
    5. A-Line taco
    6. B-Line pizza del mare
    7. B-Line pizza margherita
    8. B-Line taco

    As orders enter, your job is to schedule the product per line for the next day. As a simplification, let's assume each line produces 1000 food items per hour, no matter what product it produces. On a line, you must allow 1 hour between two different product types for clean-up and setup for the new product.
    Orders
    1. 1000 pizza del mare
    2. 3000 pizza margherita
    3. 5000 frozen pizza margherita
    4. 4000 taco

    A schedule must address the combination of a line AND a product that can be made on that line. So you can not make a mistake by scheduling a frozen pizza on the B-line.
    For that we need an extra entity "Line_Product_Schedule".
    Line_Product_Schedule:
    1. A-line frozen pizza margherita 09:00 14:00
    2. A-line taco 15:00 17:00
    3. B-line pizza del mare 09:00 10:00
    4. B-line pizza margherita 11:00 14:00
    5. B-line taco 15:00 17:00

    On the A-line we start at 09:00 till 14:00 with 5000 frozen pizza margherita. The team cleans up and sets up between 14:00 and 15:00. At 15:00 till 17:00 it produces 2000 taco's.

    On the B-line we start at 09:00 till 10:00 with 1000 pizza del mare. The team cleans up and sets up between 10:00 and 11:00. At 11:00 till 14:00 it produces 3000 pizza margherita. The team cleans up and sets up between 14:00 and 15:00. At 15:00 till 17:00 it produces 2000 taco's.

    After clean up the teams can go home at 18:00.

    You can find the model of this line product schedule design here.

    We may start to think about adding a new product "Switch" for the activities needed on a line when the product is changed. So the manager can track what is going on at a line at any given moment.
    For that you must add "switch" to Product. Add "switch" to both A-line and B-line for Line_Product. Based on this we may want to rename the "Product" entity to "Activity", ....

    Start small, define how the entities interact, make up example data for each entity, and reconsider.
    Things like this tend to grow and expand: wouldn't it be nice to add an Order entity? Wouldn't it be nice to add the date and time a line can produce (and stops producing) a product (e.g. new freezing apparatus is installed on B-line and will be available in 3 weeks, due to a fire A-line can no longer produce frozen food from today on, ...). Guard the scope of what you want to model.
    Try to keep it simple and small, but not too small that it becomes uninteresting and unrealistic.

    I hope this will get you started with modelling the rest.
    • how do teams interact with a line? (today Team 1 takes care of A-line, tomorrow it takes care of B-line in the afternoon, ...)
    • how do employees interact with a team (from 09:00 - 12:00 John is in Team 1, from 12:00 - 17:00 he is on team 2, ...)
    • how does "dough garbage" interact with a line? (you seem to be interested in modeling that, what do you want to be able to report at the end? ... )
    • ...

    Show us that you have put in time and energy and we will help you further.
    Last edited by Wim; 05-20-16 at 05:25.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •