Database query

[Gazdaman]

Ok, say I've got a table called Orders. I have a field for ProductID, which is linked to another database (yes we're talking relational here people).

I want to have space for at least 10 different productIDs in my order table. Does that mean I need 10 fields, Prod1ID, Prod2ID.

Or is there another, simpler way that I'm just missing.

I realise my explaining isn't exactly spot on. Just if someone orders one product, that means I have 9 fields left blank.

It's like, if you build a small house, you'd only build it on a small piece of land, you wouldn't build a tiny house on a massive plot of land. I just think I'm missing something by reserving all this space in case someone orders 10 different things.

This is for a piece of coursework btw, it's all theoretical, there is no datebase (or spoon?) at the moment it's just an entity relationship diagram. I don't think I can make the entity expand to accomodate the information being given to it. Like as it stands I've defined the boundaries, and if it's not used, it just lays blank.

Anyone have any input? Strange subject I know.

Gaz

[Kickstart]

Hi

Sounds more like you need an extra table.

Product Table (listing all the products)
Order Table (listing all the orders)
Order Item Table (listing each of the individual items on an order).

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[Jesus_Christ]

Or have a non unique order number so you can have multiple entries with different products but the same order number. That way you still have the same number of tables, no large blank spaces and your not buggered if someone orders over 10 items!
____________________
If you ate Stephen Hawking, would that count as one of your "five a day" portions?

[feef]

why not have an order ID as well as a uniqe ID in the order table. and th then use a composite primary key. That way you can have multiple records per order, rather than requiring multiple columns for a single order row.

I'm assuming MySQL for the DDI...

CREATE TABLE `order` (
`ID` int(11) NOT NULL auto_increment,
`OrderID` varchar(25) NOT NULL default '',
`ProdID` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`,`OrderID`)
) TYPE=InnoDB;
____________________
Mudskipper wrote: feef, that is such a beautiful post that it gave me a lady tingle
Windchill calculator - London Bike parking
Blog and stuff - PlentyMoreFish dating

[Gazdaman]

I never thought of those options!

Ok, back to the drawing board, I appreciate the code there feef too, but this is all theoretical, and there is no database atm. If there was it'd probably (sadly) be MS access.

Thank you all very much!

Gaz

[feef]

[Marci]

If you want to look at an existing database that's similar to what you're after, head to www.actinic.co.uk, download their free trial and install. Head to c:\program files\actinic ecommerce v7\sites\site1 and open actinicmaster.mdb (i think)...

That's a complete predone system that runs off an Access-based backend via a custom gui... you can see all the various tables for the various stages of order processing etc.
____________________
FAB-Racing MiniMotoSidecars - Back with a vengeance - F1 Class 2011...
CBR170 engine, custom chassis - www.minimotoscene.co.uk

[veeeffarr]

I've written Access DB's in 3rd NF before no problems.