One to Many or Many to Many?

baldur2630 · 04-23-2013, 05:03 AM

I am trying to create a small program. The programming language isn't a problem and the info I need is in any case SQL. I've never worked with One2Many or Many2Many relationships only simple databases.

I'm using MySQL and I've actually cut down this to the very minimum, because I just haven't a clue what I'm doing! I have MySQL installed and MySQL Workbench. I've created the basic Tables, but I haven't a single clue about how to create the Relationships, Constraints, Foreign Keys etc.

If I could SEE a MySQL Workbench diagram or if I could see the SQL to create the tables and the Relationships, Constraints, Keys, Unique Fields etc, I could get my head around it and sort it out so that I can actually UNDERSTAND how to achieve what I'm trying to do. I think you can see from the Tables below what I'm trying to do.

These are the Tables I created and from here on I am CLUELESS: -

TABLE Recipe
RecipeID INTEGER,
ItemName VARCHAR(40),
Type INTEGER,
Description VARCHAR(200),
PicLocation VARCHAR(40),
BatchSize VARCHAR(50),
Time2Cook VARCHAR(50),
Comments VARCHAR(200),

TABLE Ingredients
IngredientID INTEGER,
Name VARCHAR(60),
Supplier VARCHAR(60),

TABLE RecipeIngredients
IngredientID INTEGER,
Amount VARCHAR(50),

TABLE Types
TypeID INTEGER,
Name VARCHAR(60),

Can anyone PLEASE help me sort this? I'm going crazy!

chrism01 · 04-23-2013, 08:40 AM

You need to read the MySQL Manual, starting with https://dev.mysql.com/doc/refman/5.1/en/ (it has loads of examples)

Here's the page on Foreign Keys
https://dev.mysql.com/doc/refman/5.1...eign-keys.html

tronayne · 04-23-2013, 09:15 AM

I've been thinking about how to answer your question for about an hour and darned if I can figure out where to start. I have to ask: have you been though a tutorial (there are a couple of good ones at http://www.quackit.com/database/tutorial/ and http://www.w3schools.com/sql/sql_syntax.asp)? If not, take some time and try one (or both) of those -- or another one (there are many available; use Google for "sql data base tutorial" or "relational data base tutorial").

Here is a key thing you need to bear in mind when you're designing a data base: all your tables should contain only unique information (no duplicate data). Your tables should relate to other tables using one or more fields (columns) common to both.

If I were to design a recipe data base, I'd have a table that contains a numeric identifier, a name, and possibly other information unique to the name of the recipe. I would have another table that contains a numeric identifier, a name for ingredients (flour, salt, pepper, oil), possibly a supplier identification number (that would join to a supplier table containing a numeric identifier and all the unique information about that supplier -- name, address, telephone...). I would have another table defining quantities with the recipe identifier, ingredient identifier and quantity required.

Relational data base management system (RDBMS) are really, really good at joining tables together with SQL where clauses; you select the field values (ingredient, quantity) from the individual tables by joining them on the numeric identifier fields.

I have rule that I use for field names. I call it three-underscore-three. I abbreviate like this: a recipe identification number is rec_idn, a recipe name is rec_nam. An ingredient identification number is ing_idn, the name is ing_nam and so on. Why? 'Cause it's easy to type, that's why.

I hava another rule: never, ever, under any circumstances use a smallint data type (smallint is a maximum of 32,767; somebody, someday is going to overflow that and your data base will crash), use integer. Always use varchar. Always use default values, numeric values are integer, float, double (don't define a size with them unless you really know what you're doing and why). If you have a table with less than 10,000 rows, it most likely doesn't need an index (the index may wind up taking more storage space than the table does).

Just so you know, the maximum value of an integer is 2,147,483,647 -- think you'll have anything larger?

Don't over-index. It's tempting to add a bunch of indexes but it's frequently inefficient to do so. Only add indexes to fields that really will benefit from them (and, yeah, it's hard to know that -- you have to experiment to find that out). Sometimes composite indexes are useful (where two or more columns are included in the index), sometimes they're a burden, experiment.

Experiment? It means write queries and time them; if a query takes a long time to execute, you probably need an index (or rewrite the query!). You time a query something like this:

Code:

time mysql --password=blah dbname < queryfile.sql

Start small and build from there. Don't try to do the whole thing at once -- you're going to be changing things as you go. Keep in mind that the best tool for designing a data base is a pencil, no some fancy-schmancy IDE. One step at time and you'll get there.

Try your best to get a good schema -- you may want to port your design from MySQL to, say, PostgreSQL and a clean schema will let you do that.

I've been designing and developing data bases for... oh, lordy, since the 1970's and I hope I've learned a few lessons during that time. Some of those are discussed above. One last thing, though, is design with PHP in mind -- a web-based user interface (Apache, PHP) is a helluva lot easier than pretty much anything else you can come up with (yeah, there's a learning curve there but it's worth your time to learn PHP after you've got the data base as efficient as you can get it.

Hope this helps some.

baldur2630 · 04-23-2013, 09:31 AM

Thanks for your response, it has been a lot more useful than most.

I've been through a lot of tutorials, problem is that most of them assume that you are either so dumb that you don't know where the on/off switch is or how to use it and others need you to have a degree in advanced astro-physics. I retired already in 2006 I'm now 72. My field was Novell eDirectory and writing Delphi utilities for eDirectory as well. It's a bit more difficult now to wade through volumes of utterly useless (to me) information to extract a single grain of salt.

I've more or less sorted out the 1:m and m:m relationships. I've not got any problems with normalization, but I'll be darned if I can find anything SIMPLE and MEANINGFUL that tells me for ON UPDATE and ON DELETE, whether to use NO Action, RESTRICT, CASCADE or NULL along with when to use what and why.

I'm not really interested in designing a database to map the galaxy and the possibilities of life, just a simple recipe program that I can add to and develop, but if you don't understand half of the concepts in the first place or need to spend months wading though material written by a demented gnome who has a grudge against humanity, it goes from being a bit of a learning curve to needing a four year course at a University.

Seems most writers have never heard of KISS - Keep it Simple Stupid. Novell were the worst, MySQL a close second. Millions of hyperlinks all leading to everything EXCEPT what one is looking for.

theNbomr · 04-23-2013, 09:49 AM

Trying to think a little outside the box, have you considered using a web framework such as django to create your database structure? I don't know whether your application is expected to use a web interface, but even if it doesn't, there is a good abstraction layer that makes creation of the database tables fairly easy. You can then decipher how it's done, and either just use the database as it is as a django application, use the database by doing your own SQL access from whatever language you're happy with, or learn from the model that is used to craft your own database and code. There is fairly good documentation that explains the relationships at both a conceptual and functional level. If you're comfortable with Python, you might even find it useful to use the database access API within your application, and never have to write a line of SQL.
I'm sure there are other frameworks that would provide similar means to develop your understanding, if not your whole application. Ruby on Rails is certainly one well known such framework.
--- rod.

tronayne · 04-23-2013, 12:09 PM

Oh! OK! You're thinking about triggers and stored procedures? OK, here's a little scoop about those things.

The phone rings, you get up to answer it (the trigger causes you to do something). The "something," in DBMS parlance, is execute some SQL statement(s). A statement might update a column in some table or update a bunch of columns in some table. The idea is that the DBMS kinda-sorta automagically does "something" where "something" happens.

Frankly, you want to minimize the use of triggers and stored procedures (they can be horridly inefficient and place a huge burden on the data base "engine" -- lots of folks refer to the DBMS as an "engine"). For example, a system I designed and built the front-end for and maintained for some years was rewritten by folks who tried to do the entire thing with triggers and stored procedures; what ran from raw data to finished reports in 10-20 minutes (on big honking Sun servers) ran for 16 hours before somebody aborted it (and that was on a bigger honking Sun server with nothing else running on it). We are talking big data here, but the problem was the triggers and procedures, not the data.

Here's an example of a table, item, that shows what I mean about table design:

Code:

create	table	item (
	item_num	varchar(30),	-- item number, UPC-A compatible
        item_desc       varchar(50),    -- item description
	item_group	varchar(20),	-- item group or category; e.g., grocery, dairy, etc.
	item_price	decimal(10,2),	-- item selling price, each
	item_cost	decimal(10,2),	-- itme cost price, each
	item_tax	varchar(1),	-- itme is taxable (Y, N)
	item_expt	varchar(1),	-- itme is exported out of state (Y, N)
	item_wgt	decimal(10,2)	-- itme unit weight
);

create	index item01 on item (item_num, item_desc);

The data base name is ims, the table is named item and the comments describe what's what.

Why'd I use decimal(10,2)? 'Cause that's all I need, that's why; could be bigger, could be small, but, you know, off you take on you can't put so that's the best compromise in this case.

Another table is customer:

Code:

create	table	customer (
	cust_num	serial,		-- customer account number
	cust_nam_01	varchar(30),	-- customer name, line 1
	cust_nam_02	varchar(30),	-- customer name, line 2
	cust_addr_01	varchar(30),	-- customer address, line 1
	cust_addr_02	varchar(30),	-- customer address, line 2
	cust_city	varchar(20),	-- customer city
	cust_state	varchar(2),	-- customer state or provience
	cust_post	varchar(15),	-- customer postal code
	cust_country	varchar(3),	-- customer country code (default US)
	cust_phone	varchar(12),	-- customer telephone
	cust_fax	varchar(12),	-- customer facsimile
	cust_email	varchar(30),	-- customer e-mail address
	cust_web	varchar(30),	-- customer web page address
	cust_credit	decimal(10,2)	-- customer credit available amount
);

create	index cust01 on customer (cust_num, cust_name_01);

Those are what schema files look like, pretty simple, created with a text editor (vi in this case). It's about as simple as I can get it and works in a LAMP model in both MySQL and PostgreSQL.

The front end is a web page with PHP forms for user interface. The beauty of Apache/PHP is that there is no software installation on any computer except the server and anybody using any browser can use the forms without any fiddling around (well, maybe not early Internet Exploder but who the hell cares bout the boneheads still using that crap anyway).

Think through your tables -- what do you need in a given table (you know the rote, KISS it and KISS it again).

Here's an example of a select that identifies duplicate rows in a table (it uses what's called a self-join). It happens to be used to identify duplicate latitude and longitude values in a data base table of geographic information but the method is useful for any table (just change the name of the table and the names of the columns in that table):

Code:

select  x.dd_lat, x.dd_long, x.ufi, x.full_name_nd
from    gnis x
where   x.fc = 'P'
        and     x.nt = 'N'
        and     x.pc in (1, 2)
        and     1 < (
                select  count(*)
                from    gnis y
                where   x.dd_lat = y.dd_lat
                        and     x.dd_long = y.dd_long)
order   by x.dd_lat, x.dd_long;

So, yes, you can join a table to itself.

The syntax gnis x and gnis y simply lets you refer to column names without having to type out the table names; work the same to two or more tables too.

The trick with this stuff is selecting from multiple tables in a manner similar to the above; you don't have to select everything, you only select what you need and you join on the common column in both (or more) tables, say, itm_id in table a and itm_id in table b. One you do it a few times you'll probably have a better feel for it.

Anyway, hope this helps some.