DataBase Design Help

Operating Systems & Open Source Linux
,
1

I am looking to teach myself SQL, planning on using mySQL on a linux VM.

as an example, i'm trying to emulate the system we have at work, to better understand how the system works
(at present this service is outsourced to an external company, and i want to take on that responsibility)

so basically, i need advice on how to set up the tables, i have a general understanding of values, and a vague understanding of how they can relate to one another.

working in the IT industry however means there can be a hell of a lot of information for each item in the database, obviously having all of it in a single table isnt a good idea, so how do i seperate that out in the most optimal way?

brief example.

each item will have a unique barcode, an asset tag, a serial number, a collection ID, a Part Number, a State, a Status, Report ID, CPU info, RAM info, Optical info, COA info, Item Type, Form Factor, Colour, Cost, Sale Value.

then there are other details that need to be recorded,

customers, suppliers, sales, returns, item grading...

heres a link to an excel file where ive laid out what i think to be a good way to set these tables up..

please have a look, and let me know your thoughts!

https://drive.google.com/open?id=0B3YhAJSwwwhGdXhEcXpZV3NJeG8

2

Are you just trying to import a spreadsheet into a database?

3

yes, also to export reports is the idea.

ultimately im going to build a site with php to work with the database as a kind of stock control system.

and then maybe even C# or C++ but one step at a time right?

but for now im just trying to understand the back end, but it is with the idea of building a front end in mind.

4

If you want to import the spreadsheet to the mysql you can use navicat trial to do that. You can also use it to quick generate sql queries with drag and drop actions and test them out.

5

Here are my quick thoughts:

Specific
There is a general rule to not use any business value for an primary key in tables. For one, using strict technical DB ID in form of INT is generally faster than a string. Second, it is good to avoid business values in the first place, because for example people change their names, phone numbers, etc.
If in any case application make a lot of queries based on those business values, you simply do index on them.

Columns like "grade1"..."grade8" look suspicious.
Grade state - probably better to have here foreign key to GradeStates table (even if with two rows).

And generic...

vague understanding of how they can relate

I assume that you mean how technically relations between tables could be build. Otherwise without understanding relations between business entities you will not be able to design tables useful to your cause.

There is a whole process called "database normalization". Then are also performance consideration.

You might, before creating this spreadsheet start with a diagram where you will put names of the entities and draw connections between them (just names of those entities).
Name those connections. E.g. Collection-----ownedBy--->Customer. Item---partOf--->Collection.
Then describe what kind of connection quantitatively (relation it is: 1:1, 1:N, N:M) those are.
E.g. do each item have have almost always unique specification? (1:1) or... Item instance (laptop with serial number) of type "Laptop Model1" (all Models1 have the same specification) (1:N - one spec for all instances).
Then having that you will start putting properties/columns on entities.

Otherwise that spreadsheet could be a super perfect design but not for your business case. And to comment on that we would need to have the knowledge that made you to do that specific table design in the first place.

Look for tutorials for "database normalization" - maybe you will find something good (and by this I mean practical, not the ones that just output information science knowledge).

4 Likes
6

In general the schema you've laid out in the spreadsheet makes sense regarding the relations you've made and which tables you've made (except 'Item Condition Table'). One thing to consider, which is quite common practice, is to create a table for metadata for pieces of information which don't apply to all (or at least most) of the items in your table. For instance, if the attribute 'colour' is only relevant to some of your items, then it would be better to store it as metadata. The primary motivation for including a table called 'Item Metadata' is the fact that database schemas are notoriously difficult to update once they hold data, mostly because of software which relies on the schema being static. A metadata table allows you to store any attribute for the items in your database.

The table Schema would be something like (postgres-ish):

Items_Metadata (
    metadata_id  integer   PRIMARY KEY,   #maybe you could use a composite key (item_barcode, meta_field)
    item_barcode  varchar(128) references Item_ID_Table (barcode),
    meta_field  varchar(200) NOT NULL,
    meta_value  text NOT NULL
)

Example tuples:

| metadata_id | item_barcode | meta_field | meta_value |
| 1           |  1234567...  | colour     | red        |
| 2           |  1235813...  | colour     | yellow     |

Some downsides of this are:
1. Potentially complicated JOINS and subqueries may be needed.
2. Speed. This will definitely be slower than grabbing everything from a single table.
3. There is no data type constraint on the meta_value column.

One last thing, every table should have a primary key of its own, otherwise queries to CRUD a particular item become unnecessarily complex where there is no primary key.

2 Likes
7

You wanna read up on relational databases, and normalization.
normally normalizing to around tier 3 is where you wanna be, unless you really wan't to be a (my,Oracle,M$)/SQL specialist, and i mean specialist using nato standards and what not.
Tier 3 is roughly where most companies are.
Syntactically SQL is "fairly" easy, and doesn't require much to learn about, but the logistics of tables and what not suuuuuucks to learn, but is a great tool to have pretty much for any development.

4 Likes
8

In order to model a database you need to fully understand the needs of your buisness, and also have the technical knowlage and also do a lot of iterations with testing of the model.

9

You want this.

And I cannot add enough pluses to Lauritzen's comment. Database normalization. Learn it, love it. It's not hard, just step back and take a few moments to think about your data, and lay it out in a sane manner. I've worked for a company whose product was based on a database that was not even 1NF. It was horrendous. They were doing everything to try and make the database faster, except redesigning the tables.

1 Like
10

thanks to all for the comments!
normalization and relational databases it is. i'll go read up and redress this.

1 Like
11

http://www.w3schools.com/sql/