vegetable attribute:
the name of the vegetable(v_name), the producer name of the vegetable.(p_name), the address of the producer(p_addr), producer phone(p_phone), production time(p_time),vegetable price(v_price) ,vegetable intro (v_intro) , vegetable type(v_type) it have spring vegetable and winter vegetable. the above is the fields of the database, now, how to design this database. thank you.
the needs of the database: according to the producer name , which can list all of the vegetables that under it.
you’ll need to stuff the season (or whatever) into another tbale since it may be a 1:many relationship between the vegetable and the seasons. some vegetables gro in more than one season for example. I grew all year round
First of all I would create separate tables for the vegetables and the producers. If you were to put the producers info in the vegetables table and the producer gets a new phone number you need to update all the vegetables from that producer, whereas if you make the producers a separate table you only need to update it once in that table. It’s just overall a lot cleaner.
So basically what you would get is something like:
vegetable
---------
id
producerId
name
intro
p_time
price
type
producer
--------
id
name
addr
phone
Where vegetable.producerId references to a producer in the producer table (so you need a Foreign Key there).
The data types for all the columns are pretty straigtforward so I’d suggest you head over to the MySQL manual on data types to see what you can come up with.
The only field that is not straight forward is the vegetables.type field. Currently (as I understand it) that field can be either “winter” or “spring”, in which case you could use an ENUM(‘spring’,‘winter’) datatype for that field.
However, since ENUMs are considered evil by some, you could also
create a seperate table
type
-----
id
description
and change “type” to “typeId” in the vegetables table, referencing the types table, or
2) make type an INT field and create some mapping in your application, like 0=spring, 1=winter
Other things to consider - is a vegetable produced by more than one producer? There’s a standard solution to this sort of problem, and we await your attempt at that part.
Secondly if the vegetable type is spring or winter (or some other season), could you possibly have two types, both called spring that would need an id to identify them??? No.
So do you think you need to add a surrogate key to a table called type (bad name to use in a database, type is a reserved word, call it vegtype) or would the name spring suffice as the primary key and what would you gain in processing a query if the vegtype primary key was the seasonal name itself?
Now think about the alternative suggestion above - encoding a value as a number - try to guess what I mean for the vegetable type by 5. you can’t unless you have a translation table. I meant, of course, early winter. Totally not intuitive, wasn’t it and six months into live use, late summer type is required because a new producer comes up with that type of veg, so it gets 6 for it’s encoded name. Can you see the disadvantages to this sort of encoding of a value.
So post your own attempt at improving the above design, and we will give you more help with your homework.
You could improve the design for future changes. Referring to the season of the vegetable as “type” could cause problems down the road.
First, calling it “type” or even “vegtype” is not very descriptive. Maybe harvest_season or planting_season would be more appropriate.
Second, there may be some other classification in the future that would be more appropriate for type (such as categorizing the vegetable types by bulb, fruit, root, stalk, leaf, etc.). You wouldn’t be able to eyeball the table schema and know whether a field called type or vegtype referred to season, or the kind of type I mentioned.
Lastly, if you have a field with winter and spring as possible values, eventually there will be a need for summer and fall. What happens when a producer has pumpkins? Also, and I understand that this is less likely to occur in many locations, but what if the seasons are “dry” and “monsoon”?
The others above are saying the same as me about more than two veg-types existing. Do not base your design on a small sample of data, think ahead to what might happen.
And try thinking about two producers each producing the same item - again it may not currently be in your data sample, but it is a real possibility. So this becomes many vegetables produced by many producers - think about the standard solution to this and not just what your current data sample seems to suggest.
