Hey guys, I'm trying to learn MySQL and relational data

bumping while I reed

>>59476957
Cheers, mate.

Also I'll be putting constraints in after I get the basic structure down. (CASCADE etc).

>>59476972

My first suggestion could be to do something like this.

CREATE TABLE product_components(
    pid int NOT NULL PRIMARY KEY,
    cid int NOT NULL PRIMARY KEY,
    count int NOT NULL,
    FOREIGN KEY(pid) REFERENCES product(pid)
    FOREIGN KEY(cid) REFERENCES component(cid)
)

And then you can just list off how many of each component you need to make a particular product.

>>59477038

That makes sense, I haven't used a composite Primary Key yet, could you show me an example of a select statement to return the count of a particular product? And would this work if a product was made up of multiple components?

Apologies for going slow, I'm new and been at it for a while.

I'm keen to start using MySQL in my programs, the uses are unreal.

And if you don't mind, does the rest of it look reasonable? I'm not breaking any conventions etc?

You're a champ, have a good night.

Smoking has proven health benefits.

>>59477143
Yeah I don't mind a dart every now and then.

>>59477127

>could you show me an example of a select statement to return the count of a particular product?
You want to find the number of a particular component you need to build a particular product?
Or do you want to find a list of all components in a particular product, and how many of each you need?

I'm not any kind of SQL professional, I've only been using it for about a year.

>>59477191
The second, or both, I'm just trying to get a better idea of ways of retrieving useful information from my databases.

>>59477239

OK. Are you familiar with joins yet? Because now would be a convenient time to illustrate those.

>>59477247
Not yet, I've been focusing on good database design, learning the theory behind it.

In your DDL, components aren't linked to manufacturers, products aren't linked to vendors. Either create a foreign key in the Component table referencing its manufacturer (if a component can have only one manufacturer) or create a table to manage the relations between components and manufacturers (this table would consist of two foreign keys forming the composite primary key).

OP have you read about the entity-relationship model before digging into SQL? It makes things 1000% easier when you have a good understanding of those concepts first. Rushing into SQL now will only fuck your brain up.

>>59477343
Yep, I've actually made an ER diagram for this particular model, I'm just trying to convert it to SQL which is where I get hiccups.

I might review the material I read for it. This particular exercise I'm doing is from the course that is being run currently this semester at my uni.

>>59477297
Thanks for that, any tips for picking up on missing these sorts of things other than being more meticulous when converting from the ER model to the relational data model?

>>59477259

OK, good stuff.

So, you know the product's pid and want to find how many of each component you need to make it.

SELECT `cid`, `count` FROM `product_components`
WHERE `pid` = '23';

Each row of the the result set will be the component cid and the number of that component which you need to make the product.

Suppose you then wanted to find the name and description of each component in your product. You could do a followup query, knowing the cid.

SELECT `name`, `description` FROM component
WHERE `cid` = '40';

And each row will be that component's name and description.

But that's inefficient. Generally speaking, you want to obtain all the data in a single query.

SELECT `component`.`cid`, `count`,  `name`, `description` FROM `product_components`
INNER JOIN `component` ON `product_components`.`cid` = `component`.cid`
WHERE `pid` = '23';

Each row of the result set will give you the component's cid, name, description and the number of that component in your product.

The JOIN clause signifies that the two tables will be connected where the cid are both the same, constructing a new set for the sake of your query. The INNER bit isn't significant in this query because of the relations on these particular tables, but it controls the conditions of connecting the tables.

There's also the fact that I'm using

`component`.`cid`

instead of just `cid`. SQL sees an ambiguity here, because I could also be referring to the cid in product_components. It's not smart enough to realise they're the same thing, and you could just as easily write

`product_components`.`cid`

>>59477463
Show us that diagram.