Data modeling TECHNIQUES

Question

Data modeling TECHNIQUES

Rankerg

Ten Centuries

Points: 1103
More actions
May 18, 2016 at 6:12 pm

#310773

Hi
I am trying to DATA MODEL from USER VIEWS / UI, is there another approach without UI? Because business has not confirmed the UI yet.
I have identified basic ENTITIES (nouns) and some INTERSECTION Relational tables (verbs), and How do I test if I data model is GOOD? I Know with USER VIEWS/ UI I can reverse engineer i.e. CREATE A SQL that can create the UI ( from where I originially designed the user view but what IF i dont have confirmed UI yet and BUSINESS is taking time and I dont want to waste my time )

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

I tend to validate my design by collecting a list of questions the database has to answer. If I can work through how to design the queries (not necessarily do it, but which entities are involved), then I know I'm on the right track. If I"m not sure, I will add a few records to the tables I need to answer a question and see if I can write a query that will return the expected result. Definitely start with as few records as you can - you can delete them all later before loading the real data.

Not incredibly technical, but it works.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

If you have the business requirements for the data, by and large you should be able to design the tables without reference to a GUI that's going to query them. IF the business says, for example, each customer can have more than one phone number, then you create a many-to-many relationship between phone numbers and customers. If the customer can only, ever, have a single phone number, you can put that number in the customer table. None of this requires seeing the GUI to know that you're doing the right thing. It always goes to what the business needs out of the data.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Grant Fritchey (5/24/2016)
If you have the business requirements for the data, by and large you should be able to design the tables without reference to a GUI that's going to query them. IF the business says, for example, each customer can have more than one phone number, then you create a many-to-many relationship between phone numbers and customers. If the customer can only, ever, have a single phone number, you can put that number in the customer table. None of this requires seeing the GUI to know that you're doing the right thing. It always goes to what the business needs out of the data.

I absolutely agree with what Grant stated above. In fact, I'll also state that if you're taking GUI or report layouts into consideration for your database design, then you're usually doing things the wrong way. If you design the database correctly, it's not going to matter how GUI or reports are designed because the database and related code will be able to handle just about anything your heart or the business desires.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rankerg Ten Centuries Points: 1103 More actions · Answer 4

Hi

So I will have 1xtra phone number table with customer ID as a foreignkey right? And what do you mean by business requirements for data usually it's business requirements? The challenge I have it since it is migrating an existing old hierarchical database to relational data model I need to analyze the data, so for example the customer table has 5 date of birth columns and don't know which DOB column has true DOB...

Can you guys send me good data driven data modeling website links please?

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

I don't have a web site I recommend for data modeling. I do have a book I recommend though: https://www.amazon.com/Server-Relational-Database-Implementation-Experts/dp/1430236957/ref=sr_1_1?ie=UTF8&qid=1464101129&sr=8-1&keywords=louis+davidson+database+design

It sounds like you have two problems, data modeling and data cleansing, which will bring on a third, data migration. That's a lot more than just data modeling.

And your structure suggests that a Client can have X number of phone numbers, yes, but it also sounds like the same phone number could be listed multiple times. I would shy away from that design. Instead, a phone number exists as itself (it's own table) and then relationships are made to it. That way, a given phone number can be transferred (which happens), also, even if a number is shared by multiple people, you can be sure that it's only entered once and entered correctly that one time. if edits are done, they're done once, to one location. All part of the process of data design.